Text to date then subtract...

[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.

[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.

[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


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.

[ryan]

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(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.
>
>

[Ron Rosenfeld]

On Wed, 7 Dec 2005 07:31:02 -0800, "ryan" <[email protected]>
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

[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
[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.