time and date difference

  • Thread starter Thread starter Support
  • Start date Start date
S

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
 
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
 
thank you the second one is great. Exactly what I'm after.

KL said:
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 said:
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
Click to expand...
Click to expand...
 
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

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

KL said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Bob Phillips said:
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

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

KL said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

KL said:
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

Bob Phillips said:
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

Support said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top