Converting 16 digit numbers to a date format

**koochandkai** · 05-17-2012, 10:34 AM

I've been given date data as 14 (not 16) digit numbers: year(4), month(2),day(2), hour(2), minute(2), and second(2). I need to convert these fields to date formats so that I can identify day and hour and also compare times for two different fields. I'm stuck! Thanks so much.

**Bob Phillips** · 05-17-2012, 10:38 AM

Try

=DATE(LEFT(A10,4),MID(A10,5,2),MID(A10,7,2))+TIME(MID(A10,9,2),MID(A10,11,2),RIGHT(A10,2))

**daddylonglegs** · 05-17-2012, 10:45 AM

You could use TEXT function, e.g. with data in A1 use this formula in B1

=TEXT(A1,"0000-00-00 00\:00\:00")+0

format B1 in required date/time forrmat e.g. d/m/yyyy hh:mm:ss

**pmizzel** · 05-17-2012, 11:37 AM

You'll need to break the Date (yyyy/mm/dd) and the Hours (hh:mm:ss) apart. I would use...

=TEXT(LEFT($A1,8),"0000-00-00")+TEXT(RIGHT($A1,6),"00\:00\:00")

DATE(1) FORMULA =TEXT(LEFT($A2,8),"0000-00-00")+TEXT(RIGHT($A2,6),"00\:00\:00")
DATE(2) FORMULA =TEXT(LEFT($B2,8),"0000-00-00")+TEXT(RIGHT($B2,6),"00\:00\:00")
DIFF FORMULA = DATE(2) - DATE(1)

RESULTS...

DATE (1) DATE (2) DATE (1) FORMULA DATE (2) FORMULA DAYS DIFF
EX1 20120516000000 20120517000000 2012-05-16 00:00:00 2012-05-17 00:00:00 1.00
EX2 20120516111535 20120517111535 2012-05-16 11:15:35 2012-05-17 11:15:35 1.00
EX3 20120416122000 20120517111535 2012-04-16 12:20:00 2012-05-17 11:15:35 30.96

Hope this helps!
PMizzel