how to find difference between two dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Deva - 05 Nov 2007 09:10 GMT

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.

Reply to this Message

Pete_UK - 05 Nov 2007 09:18 GMT

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,
>
[quoted text clipped - 6 lines]
> Thanks abd Regards,
> Deva.

Reply to this Message

Deva - 05 Nov 2007 09:49 GMT

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
>
[quoted text clipped - 24 lines]
> > Thanks abd Regards,
> > Deva.

Reply to this Message

Ron Rosenfeld - 05 Nov 2007 10:55 GMT

>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

Reply to this Message

www.exciter.gr: Custom Excel Applications! - 05 Nov 2007 12:40 GMT

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,
>
[quoted text clipped - 18 lines]
> The above assumes your data is given in exactly the format you posted.
> --ron

Reply to this Message