Text to date then subtract...

R

ryan

Hello all,

Time to ask the experts, I'm completly lost!

I've got two text fields that are actually dates & times:
A1 = 20030715085639
B1 = 20030715085707

I need the difference in seconds between the two ... in this example 28.
 
D

Dave O

Format your answer cell as time, showing hours, minutes and seconds
(the custom format looks like h:mm:ss;@). Then enter this formula:
=(DATE(MID(B1,1,4),MID(B1,5,2),MID(B1,7,2))+TIME(MID(B1,9,2),MID(B1,11,2),MID(B1,13,2)))-(DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2)))

This formula parses the date/time stamp string into date and time
formats recognized by Excel, then subtracts the start from the finish.
 
R

Roger Govier

Hi Ryan

In cell A2
=("00:"&MID(A1,11,2)&":"&RIGHT(A1,2))+0
Copy to cell B2
In cell C2
=B2-A2

Regards

Roger Govier
 
R

Ron Rosenfeld

Hello all,

Time to ask the experts, I'm completly lost!

I've got two text fields that are actually dates & times:
A1 = 20030715085639
B1 = 20030715085707

I need the difference in seconds between the two ... in this example 28.
Click to expand...

At least in the US:

=ROUND((TEXT(B1,"0000\/00\/00 00\:00\:00")-
TEXT(A1,"0000\/00\/00 00\:00\:00"))*24*60*60,0)


--ron
 
S

Sloth

Yet another answer. Use this formula (same end result as Dave O's formula; a
number excel can interpret as date and time)
=VALUE(TEXT(B1,"####-##-## ##\:##\:##"))-VALUE(TEXT(A1,"####-##-##
##\:##\:##"))
and format the cell as

to show only seconds.
 
Top