time and date difference

[Support]

I have a cell format of dd-mm-yyyy hh:mm that I have applied to "call open
date and time" and a "call closed data and time" columns.
I'd like to have a further column that calculates the difference in days and
hours, or just hours, between these two date/time values.

I can't seem to find a way to do it tho.
I have tried DATEDIF but it doesn't give me the results I want (formula
=DATEDIF(A1,B1,"d")) and I have seen examples of calculating the difference
between time values but again, not what I want exactly.
Is there a way to do this or do I have to split the cells so I have date in
one cell and time in another?
Many thanks

 

[Bob Phillips]

Just subtract one from the other, and format as d hh:mm:ss

 

[KL]

Hi,

Try this formula =Date2-Date1 and then format the result like: [h]:mm -
you'll get the difference in hours and minutes

or

=INT(Date2-Date1) & " days " & TEXT(MOD(Date2-Date1,1),"[h]:mm")
to get days and hours with minutes in text format

Regards,
KL

 

[Support]

thank you the second one is great. Exactly what I'm after.

Hi,

Try this formula =Date2-Date1 and then format the result like: [h]:mm -
you'll get the difference in hours and minutes

or

=INT(Date2-Date1) & " days " & TEXT(MOD(Date2-Date1,1),"[h]:mm")
to get days and hours with minutes in text format

Regards,
KL

I have a cell format of dd-mm-yyyy hh:mm that I have applied to "call open
date and time" and a "call closed data and time" columns.
I'd like to have a further column that calculates the difference in days
and hours, or just hours, between these two date/time values.

I can't seem to find a way to do it tho.
I have tried DATEDIF but it doesn't give me the results I want (formula
=DATEDIF(A1,B1,"d")) and I have seen examples of calculating the
difference between time values but again, not what I want exactly.
Is there a way to do this or do I have to split the cells so I have date
in one cell and time in another?
Many thanks

 

[Bob Phillips]

You can still do it with Date2-Date1 and format, or in 1 step

=TEXT(Date2-Date1,"d \d\a\y\s h:mm")

--
HTH

Bob Phillips

thank you the second one is great. Exactly what I'm after.

Hi,

Try this formula =Date2-Date1 and then format the result like: [h]:mm -
you'll get the difference in hours and minutes

or

=INT(Date2-Date1) & " days " & TEXT(MOD(Date2-Date1,1),"[h]:mm")
to get days and hours with minutes in text format

Regards,
KL

I have a cell format of dd-mm-yyyy hh:mm that I have applied to "call open
date and time" and a "call closed data and time" columns.
I'd like to have a further column that calculates the difference in days
and hours, or just hours, between these two date/time values.

I can't seem to find a way to do it tho.
I have tried DATEDIF but it doesn't give me the results I want (formula
=DATEDIF(A1,B1,"d")) and I have seen examples of calculating the
difference between time values but again, not what I want exactly.
Is there a way to do this or do I have to split the cells so I have date
in one cell and time in another?
Many thanks

 

[KL]

Hi Bob,

Your formula shows "31 days 2:21" between '21/05/2005 23:12:00' and
'21/12/2005 01:33:00' while it should be "213 days 2:21". I guess something
must be missing in the formula.

Regards,
KL

You can still do it with Date2-Date1 and format, or in 1 step

=TEXT(Date2-Date1,"d \d\a\y\s h:mm")

--
HTH

Bob Phillips

thank you the second one is great. Exactly what I'm after.

Hi,

Try this formula =Date2-Date1 and then format the result like:
[h]:mm -
you'll get the difference in hours and minutes

or

=INT(Date2-Date1) & " days " & TEXT(MOD(Date2-Date1,1),"[h]:mm")
to get days and hours with minutes in text format

Regards,
KL


I have a cell format of dd-mm-yyyy hh:mm that I have applied to "call open
date and time" and a "call closed data and time" columns.
I'd like to have a further column that calculates the difference in days
and hours, or just hours, between these two date/time values.

I can't seem to find a way to do it tho.
I have tried DATEDIF but it doesn't give me the results I want
(formula
=DATEDIF(A1,B1,"d")) and I have seen examples of calculating the
difference between time values but again, not what I want exactly.
Is there a way to do this or do I have to split the cells so I have date
in one cell and time in another?
Many thanks

 

[Bob Phillips]

Hi KL,

Yeah, it is limited to 31 days, it loops round.

I could say a call shouldn't stay open that long, but I'll give in
gracefully

Bob

Hi Bob,

Your formula shows "31 days 2:21" between '21/05/2005 23:12:00' and
'21/12/2005 01:33:00' while it should be "213 days 2:21". I guess something
must be missing in the formula.

Regards,
KL

You can still do it with Date2-Date1 and format, or in 1 step

=TEXT(Date2-Date1,"d \d\a\y\s h:mm")

--
HTH

Bob Phillips

thank you the second one is great. Exactly what I'm after.

Hi,

Try this formula =Date2-Date1 and then format the result like:
[h]:mm -
you'll get the difference in hours and minutes

or

=INT(Date2-Date1) & " days " & TEXT(MOD(Date2-Date1,1),"[h]:mm")
to get days and hours with minutes in text format

Regards,
KL


I have a cell format of dd-mm-yyyy hh:mm that I have applied to "call open
date and time" and a "call closed data and time" columns.
I'd like to have a further column that calculates the difference in days
and hours, or just hours, between these two date/time values.

I can't seem to find a way to do it tho.
I have tried DATEDIF but it doesn't give me the results I want
(formula
=DATEDIF(A1,B1,"d")) and I have seen examples of calculating the
difference between time values but again, not what I want exactly.
Is there a way to do this or do I have to split the cells so I have date
in one cell and time in another?
Many thanks