Issue with Date format in Excel 2003

**Sunny.dayz123** · 05-16-2012, 04:52 AM

Hello Everyone

Issue: Attached is the date sample, that I want to convert into dd/mm/yyyy. Dates are in different format ie. some are in mm/dd/yyyy and some are in dd/mm/yyyy. Also, find out the Ageing/duration by days and months with Today's date i.e 16-May-2012

Tried:- Have tried this formula -> =IF(ISNUMBER(E2),DATEVALUE(TEXT(E2,"mm/dd/yyyy")),DATE(MID(E2,7,4),MID(E2,4,2),MID(E2,1,2)))
But this is not working with every date. for example, for date like 4/5/2012 it is taking as 5-Apr-2012 wheras it should be 4-May-2012.

Resolution Required:- Plz see the sample attached.

Thankyou in Advance.

**npamcpp** · 05-16-2012, 05:44 AM

Hi,

since your values are real date/time values, you can extract the date only by using

=INT(B3) (format as date)

and

=MOD(B3,1) (format as time)

The value in B3 actually is 5-April, not 4-May. If you want 5-April, then you have to enter 5-April. You cannot change a date by formatting it.

Your spreadsheet seems to mix DMY and MDY formats. Very confusing.

Apply one consistent date format to all dates. You can then also just use =B3 and format as a time value, to display the time portion only and use date without time format for B3 itself.

Issue with Date format in Excel 2003

LinkBack

Thread Tools

Rate This Thread

Display

Issue with Date format in Excel 2003

Re: Issue with Date format in Excel 2003

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions