how to find difference between two dates

[Guest]

Hi,

Please let me know the formula for finding the hours or minutes difference
between two dates.

Eg : I want to find the difference between two timings : Nov 05 2007
4:20PM to Nov 05 2007 7:22PM

Thanks abd Regards,
Deva.

 

[Pete_UK]

All you need to do is subtract the two and format as [h]:mm

If you want the result in hours rather than Excel time format, then
multiply by 24, or in minutes multiply again by 60 - format both of
these as number with appropriate dp, i.e.:

=A2-A1 in Excel time format

=(A2-A1)*24 in hours (format as number)

=(A2-A1)*24*60 in minutes.

Assumes A2 is the later time.

Hope this helps.

Pete

 

[Guest]

Hi Pete,

but it is not giving the result.

how to find it, if it is in this format : Nov 05 2007 4:20PM

All you need to do is subtract the two and format as [h]:mm

If you want the result in hours rather than Excel time format, then
multiply by 24, or in minutes multiply again by 60 - format both of
these as number with appropriate dp, i.e.:

=A2-A1 in Excel time format

=(A2-A1)*24 in hours (format as number)

=(A2-A1)*24*60 in minutes.

Assumes A2 is the later time.

Hope this helps.

Pete

Hi,

Please let me know the formula for finding the hours or minutes difference
between two dates.

Eg : I want to find the difference between two timings : Nov 05 2007
4:20PM to Nov 05 2007 7:22PM

Thanks abd Regards,
Deva.

 

[Ron Rosenfeld]

Hi Pete,

but it is not giving the result.

how to find it, if it is in this format : Nov 05 2007 4:20PM

What result is it giving you?

So long as your data is truly an Excel data, the format should not matter.

If you want the results in hours and minutes, format your result cell as [h]:mm

If the values are text strings, and not truly Excel date/times, then you need
to convert them to real Excel date/times first.

One formula to do that, with your data in A1, is:

=MID(A1,5,2)&"-"&LEFT(A1,3)&"-"&MID(A1,8,4)&
MID(A1,12,LEN(A1)-2-11)&" "&RIGHT(A1,2)

The above assumes your data is given in exactly the format you posted.
--ron

 

[www.exciter.gr: Custom Excel Applications!]

try this link: http://custom-excel.blogspot.com/
it also has a video that shows the procedure

http://www.exciter.gr
Custom Excel Applications and Functions!

Hi Pete,

but it is not giving the result.

how to find it, if it is in this format : Nov 05 2007 4:20PM

What result is it giving you?

So long as your data is truly an Excel data, the format should not matter.

If you want the results in hours and minutes, format your result cell as [h]:mm

If the values are text strings, and not truly Excel date/times, then you need
to convert them to real Excel date/times first.

One formula to do that, with your data in A1, is:

=MID(A1,5,2)&"-"&LEFT(A1,3)&"-"&MID(A1,8,4)&
MID(A1,12,LEN(A1)-2-11)&" "&RIGHT(A1,2)

The above assumes your data is given in exactly the format you posted.
--ron