Dates & Time (Excel 2000/2002)

Topic

New Reply

Hallo
I sit with the problem that I have thousands of rows of data with dates and times in a different format than I need it.
The problem is that I have two sets of data that I have to get in the correct format.
I attach a work sheet showing the formats of the un-desired formats and the desired format.
I would appreciate any ideas how I can accomplish this.

Regards

Reply | Quote

Replies

For cells that contain date and time combined, such as A4:
Enter the formula =TRUNC(A4) in the cell where you want the date, and format this as a date.
Enter the formula =MOD(A4,4) in the cell where you want the time, and format this as a time.

For cells that contain yyyymmdd, such as C4:
Enter the formula =DATE(LEFT(C4,4),MID(C4,5,2),MID(C4,7,2)) in the cell where you want the date, and format as a date.
For cells that contain hhmmss, such as D4:
Enter the formula =TIME(TRUNC(D4/10000),TRUNC(MOD(D4,10000)/100),MOD(D4,100)) in the cell where you want the time, and format as a time

You can fill down each of these formulas as far as needed.

Reply | Quote

Hans

Thank you very much, your formulas is going to save me at least a week or two.
I really appreciate your help.

Thank you again

Reply | Quote