How do you calculate two dates with hours, minutes and seconds included...
Also, the data is all numbers like 20080514235050 to 20080515000505
(yyyymmddhhmmss)... Thanks..

You will pretty much need to parse the date out and then do your
calculation. If your date is in E4

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

Will get your number into a date, then use it again on the other date an put
the two together for the calculation so if one date was in E4 and the other
in F4 and you wanted the difference

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

This could be shortened up using a UDF

Bear in mind your formatting of the result will determine how it is
displayed. read up here for more info

www.cpearson.com/excel/datetime.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





<west4961@yahoo.com> wrote in message
news:etednZvYkYSbB7LVnZ2dnUVZ_qqgnZ2d@comcast.com. ..
> How do you calculate two dates with hours, minutes and seconds included...
> Also, the data is all numbers like 20080514235050 to 20080515000505
> (yyyymmddhhmmss)... Thanks..

You can convert to a TRUE date/time with this formula

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

so, assuming data in A1 and B1 you can get the difference with this formula

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

format result cell as [h]:mm:ss

"Nick Hodge" wrote:

> You will pretty much need to parse the date out and then do your
> calculation. If your date is in E4
>
> =DATE(LEFT(E4,4),MID(E4,5,2),MID(E4,7,2))+TIME(MID (E4,9,2),MID(E4,11,2),RIGHT(E4,2))
>
> Will get your number into a date, then use it again on the other date an put
> the two together for the calculation so if one date was in E4 and the other
> in F4 and you wanted the difference
>
> =DATE(LEFT(E4,4),MID(E4,5,2),MID(E4,7,2))+TIME(MID (E4,9,2),MID(E4,11,2),RIGHT(E4,2))-=DATE(LEFT(F4,4),MID(F4,5,2),MID(F4,7,2))+TIME(MID (F4,9,2),MID(F4,11,2),RIGHT(F4,2))
>
> This could be shortened up using a UDF
>
> Bear in mind your formatting of the result will determine how it is
> displayed. read up here for more info
>
> www.cpearson.com/excel/datetime.htm
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
> web: www.excelusergroup.org
> web: www.nickhodge.co.uk
>
>
>
>
>
> <west4961@yahoo.com> wrote in message
> news:etednZvYkYSbB7LVnZ2dnUVZ_qqgnZ2d@comcast.com. ..
> > How do you calculate two dates with hours, minutes and seconds included...
> > Also, the data is all numbers like 20080514235050 to 20080515000505
> > (yyyymmddhhmmss)... Thanks..
>
>

How could I read the result in seconds only? Thanks your help...

In article <19914654-E4F1-489D-A170-C01A79CA7F8D@microsoft.com>,
=?Utf-8?B?ZGFkZHlsb25nbGVncw==?= <d.addylonglegs@virgin.net> wrote:
>You can convert to a TRUE date/time with this formula
>
>=TEXT(A1,"0000-00-00 00\:00\:00")+0
>
>so, assuming data in A1 and B1 you can get the difference with this formula
>
>=TEXT(B1,"0000-00-00 00\:00\:00")-TEXT(A1,"0000-00-00 00\:00\:00")
>
>format result cell as [h]:mm:ss
>
>"Nick Hodge" wrote:
>
>> You will pretty much need to parse the date out and then do your
>> calculation. If your date is in E4
>>
>>
> =DATE(LEFT(E4,4),MID(E4,5,2),MID(E4,7,2))+TIME(MID (E4,9,2),MID(E4,11,2),RIGHT(
>E4,2))
>>
>> Will get your number into a date, then use it again on the other date an put
>> the two together for the calculation so if one date was in E4 and the other
>> in F4 and you wanted the difference
>>
>>
> =DATE(LEFT(E4,4),MID(E4,5,2),MID(E4,7,2))+TIME(MID (E4,9,2),MID(E4,11,2),RIGHT(
>E4,2))-=DATE(LEFT(F4,4),MID(F4,5,2),MID(F4,7,2))+TIME(MID (F4,9,2),MID(F4,11,2),
>RIGHT(F4,2))
>>
>> This could be shortened up using a UDF
>>
>> Bear in mind your formatting of the result will determine how it is
>> displayed. read up here for more info
>>
>> www.cpearson.com/excel/datetime.htm
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
>> web: www.excelusergroup.org
>> web: www.nickhodge.co.uk
>>
>>
>>
>>
>>
>> <west4961@yahoo.com> wrote in message
>> news:etednZvYkYSbB7LVnZ2dnUVZ_qqgnZ2d@comcast.com. ..
>> > How do you calculate two dates with hours, minutes and seconds included...
>> > Also, the data is all numbers like 20080514235050 to 20080515000505
>> > (yyyymmddhhmmss)... Thanks..
>>
>>

You could use a custom number format of: [ss]
And the value will still be a date/time.

Or you could modify the formula to return the number of seconds:
=(TEXT(B1,"0000-00-00 00\:00\:00")-TEXT(A1,"0000-00-00 00\:00\:00"))*24*60*60
(format as General)

west4961@yahoo.com wrote:
>
> How could I read the result in seconds only? Thanks your help...
>
> In article <19914654-E4F1-489D-A170-C01A79CA7F8D@microsoft.com>,
> =?Utf-8?B?ZGFkZHlsb25nbGVncw==?= <d.addylonglegs@virgin.net> wrote:
> >You can convert to a TRUE date/time with this formula
> >
> >=TEXT(A1,"0000-00-00 00\:00\:00")+0
> >
> >so, assuming data in A1 and B1 you can get the difference with this formula
> >
> >=TEXT(B1,"0000-00-00 00\:00\:00")-TEXT(A1,"0000-00-00 00\:00\:00")
> >
> >format result cell as [h]:mm:ss
> >
> >"Nick Hodge" wrote:
> >
> >> You will pretty much need to parse the date out and then do your
> >> calculation. If your date is in E4
> >>
> >>
> > =DATE(LEFT(E4,4),MID(E4,5,2),MID(E4,7,2))+TIME(MID (E4,9,2),MID(E4,11,2),RIGHT(
> >E4,2))
> >>
> >> Will get your number into a date, then use it again on the other date an put
> >> the two together for the calculation so if one date was in E4 and the other
> >> in F4 and you wanted the difference
> >>
> >>
> > =DATE(LEFT(E4,4),MID(E4,5,2),MID(E4,7,2))+TIME(MID (E4,9,2),MID(E4,11,2),RIGHT(
> >E4,2))-=DATE(LEFT(F4,4),MID(F4,5,2),MID(F4,7,2))+TIME(MID (F4,9,2),MID(F4,11,2),
> >RIGHT(F4,2))
> >>
> >> This could be shortened up using a UDF
> >>
> >> Bear in mind your formatting of the result will determine how it is
> >> displayed. read up here for more info
> >>
> >> www.cpearson.com/excel/datetime.htm
> >>
> >> --
> >> HTH
> >> Nick Hodge
> >> Microsoft MVP - Excel
> >> Southampton, England
> >> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
> >> web: www.excelusergroup.org
> >> web: www.nickhodge.co.uk
> >>
> >>
> >>
> >>
> >>
> >> <west4961@yahoo.com> wrote in message
> >> news:etednZvYkYSbB7LVnZ2dnUVZ_qqgnZ2d@comcast.com. ..
> >> > How do you calculate two dates with hours, minutes and seconds included...
> >> > Also, the data is all numbers like 20080514235050 to 20080515000505
> >> > (yyyymmddhhmmss)... Thanks..
> >>
> >>

--

Dave Peterson