Issue with Date format in Excel 2003

[Sunny.dayz123]

Hello All

This is my first post in this Forum.

Issue:- I have a file with 'Open Time' as a header and underneath 475 entries of "date & time" with various date formats like dd/mm/yyyy HH:MM:ss
mm/dd/yyyy hh:mm:ss and so on. There are few items that i have managed to change the Date to "Month,year" ie. January-11 but there are many items that even does not get edit and changed to "month-Year" format.


Aid: I'm attaching a sample of that file so that you can have a look and you can work.

Looking for solution : I'm looking for a solution, to have attached file date and time in two different columns- Time should remain same in that format but Date should appear like -dd/mm/yyyy and when copy and paste in other column and changing format should appear like January-12 (as an example)

[DGagnon]

someone may have a more elegant solution, but because your dates were using 2 different formats i had to use the following formulas (demonstrated in attached)

for Date

=IFERROR(DATE(MID(TEXT(INT(B4),"mm/dd/yyyy"),FIND("/20",TEXT(INT(B4),"mm/dd/yyyy"))+1,4),MID(TEXT(INT(B4),"mm/dd/yyyy"),FIND("/",TEXT(INT(B4),"mm/dd/yyyy"))+1,FIND("/",TEXT(INT(B4),"mm/dd/yyyy"),FIND("/",TEXT(INT(B4),"mm/dd/yyyy")))-1),LEFT(TEXT(INT(B4),"mm/dd/yyyy"),FIND("/",TEXT(INT(B4),"mm/dd/yyyy"))-1)),DATE(MID(B4,FIND("/20",B4)+1,4),MID(B4,FIND("/",B4)+1,FIND("/",B4,FIND("/",B4))-1),LEFT(B4,FIND("/",B4)-1)))

for Time

=IFERROR(B4-INT(B4),TIMEVALUE(RIGHT(B4,LEN(B4)-FIND(" ",B4))))

and the last column

=TEXT(C4,"mmmm-yy")

[Marcol]

I'm not sure that there are two date formats in your sheet, just that some are strings and others are numeric date/time, see Column A as a check.

Have a look at the formulae, and formatting, in this workbook.

Select all of Column B
Then use Data > Text to Columns
Click Next and Click Next again
Select the radio button (Column data format) Date select DMY from the drop-down.
Click Finish

[DGagnon]

using that result you will notice some dates, such as line 35 are showing as December 2012, instead of January 2012. i had the same issue when i first ran through this.

[Marcol]

@ DGagnon.
Is that a correct interpretaion of the data?

Check the number formatting of the numeric dates in the OPs' original file

Put this in A3

Please Login or Register  to view this content.

Drag/Fill down

Check out the rows that return TRUE.

Cell B3 is custom formatted "m/d/yyyy hh:mm:ss" while C6 is "dd/mm/yyyy hh:mm" it has to be the underlying serial number that is correct, otherwise this problem is virtually unsolvable!

Now put this in F3

Please Login or Register  to view this content.

Drag/Fill down

All the text strings are clearly "dd/mm/yyyy hh:mm" style.

[DGagnon]

It could be a regional setting, but it appeared that he had date stamps such as "12/1/2012 5:04:22 PM" on line 36 that is actualy a date value of 1-Dec-12, but the date he is trying to get from this is 12-Jan-12, so what i did was to convert that date value into a string, then took the month and day and fliped them as a string, then converted it back to a date value. This gave the desired output of 12-Jan-12. unless i mis understood the OP's request i dont see any different way to accomplish this.

note: this why i always use dates in the dd-mmm-yy format

[Marcol]

@ DGagnon.
Cell B36 has a value of 41244.7113657407 format it however you like it's still the serial date, (the integer part), for the 1st of December 2012.

What rule are you going to apply when changing this, or other numeric examples, to 12th of January 2012 or equivalent?
12th of January 2012 is 40920 not 41244

You can get an answer wth your method, but is it correct? ... I wouldn't bet my last penny on it.

[DGagnon]

I can only go by what i understood from the OP, he stated in his file"*Plz note, in this data, there are no enteries post march,12." so i have to assume that the date values are incorrect. once i came to that assumption, even though i full understand that the value in B36 is = to 1-Dec-2012 (41244) as it stand (+ the time), the OP wants that value to be equal to 12-Jan-2012 (40920). if it was not the request of the OP i would never have done this the way i did.


Edit:

after reviewing the formula provided, i could have simplified it by using the following

=IFERROR(DATE(YEAR(B4),DAY(B4),MONTH(B4)),DATE(MID(B4,FIND("/20",B4)+1,4),MID(B4,FIND("/",B4)+1,FIND("/",B4,FIND("/",B4))-1),LEFT(B4,FIND("/",B4)-1)))

[Marcol]

Okay, I think I see now where the confusion lies.

If we format Column B
Number format > Custom
Type:=

Please Login or Register  to view this content.

Alignment > Horizontal = General

We can now see the expected result, however the serial dates are not what the OP wants, it seems that this all of this column should be read as "dd/mm/yyyy hh:mm:ss"

So in C3

Please Login or Register  to view this content.

And in D3

Please Login or Register  to view this content.

Drag/Fill Both Down.

Is this correct now?

[Sunny.dayz123]

@DGagnon, How I can get this worked in excel 2003. same formula that you gave me for the first time.

for Date

=IFERROR(DATE(MID(TEXT(INT(B4),"mm/dd/yyyy"),FIND("/20",TEXT(INT(B4),"mm/dd/yyyy"))+1,4),MID(TEXT(INT(B4),"mm/dd/yyyy"),FIND("/",TEXT(INT(B4),"mm/dd/yyyy"))+1,FIND("/",TEXT(INT(B4),"mm/dd/yyyy"),FIND("/",TEXT(INT(B4),"mm/dd/yyyy")))-1),LEFT(TEXT(INT(B4),"mm/dd/yyyy"),FIND("/",TEXT(INT(B4),"mm/dd/yyyy"))-1)),DATE(MID(B4,FIND("/20",B4)+1,4),MID(B4,FIND("/",B4)+1,FIND("/",B4,FIND("/",B4))-1),LEFT(B4,FIND("/",B4)-1)))

for Time

=IFERROR(B4-INT(B4),TIMEVALUE(RIGHT(B4,LEN(B4)-FIND(" ",B4))))

[DGagnon]

the IFERROR formula will not work in excel 2003, try this solution provided by Marcol

For Date
=IF(ISNUMBER(B3),DATEVALUE(TEXT(B3,"mm/dd/yyyy")),DATE(MID(B3,7,4),MID(B3,4,2),MID(B3,1,2)))

For Time
=IF(ISNUMBER(B3),MOD(B3,1),TIMEVALUE(B3))

That shoudl work, if not i will modify my formula.