Some Dates In Format Not Being Converted Into Normal Date Format?

May 23, 2014

I have a some dates in a format which are not being converted into the normal date format. So i made this formula to convert it into the normal date format:

=IF(LEN(B218)=10,(MID(B218,4,2)&"/"&LEFT(B218,2)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=9,(MID(B218,3,2)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=8,(MID(B218,3,1)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,"")))

Formula is working fine except on this type of date "9/9/2013"..i also tried an OR formula with mid but didnt get the desired result.These are the type of dates:

12/10/2013
12/9/2013
9/11/2013
9/9/2013
7/25/2013

View 9 Replies


ADVERTISEMENT

Change From Serial Number To Normal Format

Jan 5, 2009

I have been trying to type a date in a column in MS excel. But excel always reads it as a serial number.

How Can I get it to display the date as it is in date format. I have the next column formatted/ formulated to display the day of the date in the previous column.

View 9 Replies View Related

Custom Date Format: For 2 Dates

Jun 15, 2007

I want to display 2 dates in one cell and have them custom formatted.

Eg. Display in one cell: 26 Jun - 27 Jun

With text typed: 26-6 - 27-6
(Or something along the lines of)

I know that dd mmm will display one date. But I cannot get 2 dates formatted. I tried dd mmm; dd mmm" - "dd mmm, which didn't work.

View 9 Replies View Related

Convert Non-US Dates To US Date Format

Dec 9, 2006

I am parsing a string which contains (European) dates:

"10/08/2005,11/08/2005,12/08/2005,13/08/2005,14/08/2005,".

When broken into individual cells, dates before the 13th of the month are recognised as "American" dates and display as "08/10/2005", "08/11/2005", 08/12/2005" etc. with automatic date format. Dates from the 13th onwards are left in general format as "13/08/2005", "14/08/2005".

I have written a macro that converts each date individually to its correct Excel serial number, but I can't help thinking that there must be a simpler way, and that I cannot be the first to have asked this!

View 8 Replies View Related

Non US Date Format For Dates In CSV File

Oct 21, 2007

I am using a CSV that has the date in Column A in the format of DD/MM/YYYY. When I open this CSV using a VBA comand the Format of the Cells in this col are then formated as either Date or General. The general format happends when the date cant be turned into US format and when it can then the date is turned US and displayd as such.

SO
14- Sep = 14/09/2007 as General
10- Sep = 09/10/2007 as Date

Im in Australia so the correct format is DD/MM/YYYY. Microsoft has issued a fix for this using Local:=True in the file open comand which is all good and well as it works unless you have office 2000 when it crashes. What would be the best way of fixing this, so that the Dates are all correct or the col is all the same format?

View 4 Replies View Related

Dates Are Not In Same Format Even Though Both Cells Are Formatted As DATE

Oct 17, 2013

I'm working with a big dataset and the dates come out in this way:

94WAO7t.png

EDIT: Example of book included

View 10 Replies View Related

Date Format Is Remaining As Serial Dates

Feb 1, 2007

My dates are coming up in serial format even though I have the cells formatted to date. It doesn't matter which date format I select they all remain as serial dates.

View 4 Replies View Related

Dates Display As Numbers Even With Date Format

Jun 20, 2008

dates column displays numbers even though the format in the format cell options is date format.

the number updates to date format only when i do F2 & enter for each cell. i've about 5000 cells with same problem. how cani do them all in a go instead of doing themone for a time.

View 6 Replies View Related

Unable To Change The Date Format In A Column Of Dates

May 28, 2013

I am unable to change the date format in a column of dates. The cells have values that look like the following: 4/29/2013 8:59:12 AM. I want to change these to YYYY-MM-DD format, but no matter what I try the format will not change.

View 4 Replies View Related

Userform Date Format Reverting Back To Us Format On Change Event

Aug 10, 2009

I have created a userform but I am having extensive problems with the date formats.

My system is set to UK and short date is set to: DD/MM/YYYY

When I used code to add the values in the userform to the spreadsheet, any that contained a date format would revert to the US format.

So I finally figured out to use DateValue to format it correctly for example: ...

View 2 Replies View Related

Conditionally Format Entire Row For Dates 15 Days Past Today Date?

Jun 11, 2014

I have spent too many hours Googling and trial with mostly error in attempting this. I formatted the very first cell A1 for today's date =TODAY(); this is also dirrectly above the column with all the dates in it. I just want an entire row's contents to turn red if it is 15 days old or older. This =$A1<TODAY()-15 somewhat works, but it only turns the date cell red, not the whole row. It also makes blank cells turns red, which I would like to remain blank (another formula is needed I imagine).

View 3 Replies View Related

Excel 2010 :: How To Convert Numeric Dates Into Proper Date Format

Aug 21, 2013

I have a column of dates formatted as:

20130201

The cell format in the column is General.

I need to change it so that the format looks like this:

02/01/2013

Is there a quick/easy way to do this in Excel 2010?

View 2 Replies View Related

Convert Jd Edwards Julian Dates Format Into Standard Gregorian Date

Aug 9, 2007

how to convert the JD Edwards Julian date format into standard Gregorian date, and can it be done using VBA? Note that the JD Edwards Julian date is different than the standard Julian Date and uses 6 digits instead of 7.

View 7 Replies View Related

Converting Times Stored In Date Format To Number Format?

Mar 31, 2014

One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.

View 5 Replies View Related

Macro Or VBA - Search General Format Convert To Date Format

Oct 26, 2012

Every month, I have an import a report to a spreadsheet. At my column A, it supposed to be a date format.

For some reasons, I have a combination of a few cells of date format and a few cells of general format with no order at all.

What I need is: Search in the A column, if date format leave it , if not change from general to date format.

View 2 Replies View Related

Excel 2010 :: Number Format Keeps Switching To Date Format?

Aug 1, 2013

I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.

This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.

View 3 Replies View Related

Macro To Change Date Format To General Format

Oct 17, 2012

I have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.

View 9 Replies View Related

Change A Date In Text Format Into Regular Format

Jan 10, 2007

I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.

View 9 Replies View Related

Pasted European Date Format Coverting To US Format

Mar 19, 2008

I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another

18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK

View 5 Replies View Related

Formula For Changing Date Format 3-12-13 To Format D-mm-yy

Apr 8, 2013

I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.

View 2 Replies View Related

Convert A Date Format To A Text Format

Oct 6, 2008

Example......

In D2 I have: =B2
In D3 I have manually input: (space)3-08

As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!

View 3 Replies View Related

Convert General Format Into Date Format?

Mar 23, 2012

How we can convert any data which is in General format in to date format.For ex I've:

19/03/2012 14:57 (General format)

I want the output/result

19-Mar

View 5 Replies View Related

Format Date In Cell To Custom Format?

Feb 21, 2014

I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)

Existing ----> Required
2/1/1980 --> '02-01-80
2/1/1970 --> '02-01-70
2/1/1983 --> '02-01-83

I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.

View 4 Replies View Related

Date Format: Change Format To Mm/dd/yyyy

Jul 3, 2006

I have txtboxes in which I write date in format dd/mm/yyyy but if I want to put date in cell correctly from this textbox in need to declare event on exit -change format to mm/dd/yyyy and then it puts date in desired format which is dd/mm/yyyy and my regional settings are English(uk)

Is there a way to put any date format in textbox and in cell there will always be format dd/mm/yyyy or at least if date is entered in format dd/mm/yyyy that also in cell the same format is putted

View 3 Replies View Related

Converting Normal Time To Epoch Time Format Using Excel

May 18, 2003

I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.

Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?

I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.

View 9 Replies View Related

Format Cell To Be Normal Color Unless Cell Left Of It Not Empty

Apr 23, 2014

How to format a cell to be normal color unless cell left of it is not empty and this cell is empty.

And I need to format whole sheet to this rule.

View 1 Replies View Related

Converting Normal Time To Epoch Time Format

May 18, 2003

I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.

Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?

I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.

View 9 Replies View Related

Check The Date Format Is In Particular Format

Jan 13, 2009

In one excel file, i have a date , and i set this to "dd/mm/yy hh::mm am/pm" format. From another file i will open this file and i need to copy that date to this file. My question is , before copying i need to check it is in the "dd/mm/yy hh::mm am/pm" format using vba.

View 9 Replies View Related

Vba Code To Format Date In The Format Dd-mm-yy

Jun 13, 2006

I would like to format my cells containg date from any format to the format dd-mm-yy. I was doing this manually with the below function

=TEXT(YEAR(Q2),0)&"-"&(RIGHT("0"&TEXT(MONTH(Q2),0),2))&"-"&(RIGHT("0"&TEXT(DAY(Q2),0),2)).

I would like to automate this using vba. i tried a bit but doesn't get what i want. ultimately i want the date in text(dd-mm-yy) format.

View 5 Replies View Related

Convert Date Format To Another Format

Sep 11, 2007

How do I change a date "10/01/2004" into a format like 20041001? I used concatenate(right(A1,4),left(A1,2),mid(A1,4,2)) but the function takes the serial value of the date as an argument and concatenates that . So I get a different value.How can I get away with it?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved