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.

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.

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.

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.

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

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.