Date and Time Function

  • Thread starter Thread starter Diana
  • Start date Start date
D

Diana

Can someone help me with the function/formula to do the
following: subtract 2 dates and times (date and time are
in the same cell)from one another and determine the
number of days and hours in the 3rd cell. I have perused
www.cpearson.com but am finding either one of the other
but not this scenario exactly. Thanks in advance!
 
Hi Diana
one way (if A1 and B1 stores your dates/times)
1. To get the days:
=INT(B1)-INT(A1)-(MOD(B1,1)<MOD(A1,1))

2. to get the hours:
=MOD(B1)-MOD(A1)+(MOD(B1,1)<MOD(A1,1))

to combine this one way (though the result is a text not a number):
=INT(B1)-INT(A1)-(MOD(B1,1)<MOD(A1,1)) & " days and " &
MOD(B1)-MOD(A1)+(MOD(B1,1)<MOD(A1,1)) & " hours"
 
.... or, =B1-A1 and use a custom format of

d "days," h " hours and" m "minutes"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
This is a rather poor solution as it relies on the 1900 date system
being in use. The "d" switch is for day-of-the-month, not elapsed time,
so 1.25 days, for example, will display

1 days, 6 hours and 0 minutes

in the 1900 date system, but

2 days, 6 hours and 0 minutes

in the 1904 date system. Since the date system is set by the first
workbook opened in an XL session, that might lead to erroneous
calculations, with no indication to the user, if the workbook were
opened second.

In addition, if B1-A1 is >31, d rolls over, so a value of 43.25 days
will display

15 days, 6 hours and 0 minutes
 
Thanks for the formula. When I tried it I got an error
message noting that there were too few arguments on the 2
mod functions after it returns "days and" I tried this
modification but it doesn't return the correct # of hours.

=INT(B1)-INT(A1)-(MOD(B1,1)<MOD(A1,1)) & " days and " &
MOD(B1,1)-MOD(A1,1)+(MOD(B1,1)<MOD(A1,1)) & " hours"

Anyone have any additional ideas? Thanks.
 
Hi Diana
sorry, my fault. change the formulas as follows:

Hi Diana
one way (if A1 and B1 stores your dates/times)
1. To get the days:
=INT(B1)-INT(A1)-(MOD(B1,1)<MOD(A1,1))

2. to get the hours:
=(MOD(B1,1)-MOD(A1,1)+(MOD(B1,1)<MOD(A1,1)))*24

to combine this one way (though the result is a text not a number):
=INT(B1)-INT(A1)-(MOD(B1,1)<MOD(A1,1)) & " days and " &
(MOD(B1,1)-MOD(A1,1)+(MOD(B1,1)<MOD(A1,1)))*24 & " hours"
 
Back
Top