EXCEL - Convert Serial Date to Normal Date Format

Pacifica09

New Member
Joined
Jan 19, 2009
Messages
2
I have a csv file (from fox pro db) which I converted to Excel 2007. This xls file contains the fields: EmployeeID, EmployeeName, Date, Day, Time, etc....(Looks great in PDF) HOWEVER, in xls, the date, time and day come out wrong. How can I convert the 10-digit number to normal date format?
Egs: of a 10-digit number I have in the first 3 cells are: 3441076560; 3441076261; 3441077042 and so on.
I'm wondering if the time is also included in this 10-digit serial number?

Please help.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi

Welcome to MrExcel.

I hope that I have understood your question but incase I am wrong, then please accept my apologies.

Assuming that you have the 10 digit number in cell A1, and provided that time is included in this 10 digit number, if you need to convert the date into a normal date, you can try:

=TEXT(LEFT(A1,5),"dd-mmm-yyyy")

Does that help ?
 
Upvote 0
If 3441076560 is in B2 then try

Date
=--Replace(B2,6,,".")
format the cell like "mm/dd/yyyy"

Day
=Replace(B3,6,,".")
format the cell like "d"

Time
=Replace(B4,6,,".")
format the cell like "hh:mm:ss"
 
Upvote 0
I would like to THANK Stormseed,Sal Paradise and jindon for all your help. I'm on my way to completing my task - with smiles today unlike yesterday.

Rock On!

Many Thanks,
Pacifica09
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top