Text to date then subtract...
 

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.

Text to date then subtract...
 

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(M ID(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(MI D(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.

Text to date then subtract...
 

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


ryan wrote:
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.

Text to date then subtract...
 

Dave, you rock! Thanks very much! Perfect!

"Dave O" wrote:

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(M ID(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(MI D(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.

Text to date then subtract...
 

On Wed, 7 Dec 2005 07:31:02 -0800, "ryan"
wrote:

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.

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

Text to date then subtract...
 

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
[s]
to show only seconds.

"ryan" wrote:

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.