Doing calculations with date formatting

T

TAllen

I'm trying to subtract one date from another and have the number of
days, hours, minutes, and seconds show in the answer.

Example:

2/26/04 11:30:00 PM - 2/25/04 11:30:00 PM should equal ??????

Is this do-able???
 
F

Frank Kabel

Hi
try the following (if A1 and B1 stores your date/time values)
=INT(B1-A1)-(MOD(B1,1)<MOD(A1,1)) & " days and " &
TEXT(MOD(B1,1)-MOD(A1,1)+(MOD(B1,1)<MOD(A1,1)),"hh:mm:ss")
 
F

Frank Kabel

Hi
sorry change the formula to
=INT(B1-A1) & " days and " &
TEXT(MOD(B1,1)-MOD(A1,1)+(MOD(B1,1)<MOD(A1,1)),"hh:mm:ss")

and an additional note: This result is a text value (you can't use it
in further calculations)
 
R

Ron Rosenfeld

I'm trying to subtract one date from another and have the number of
days, hours, minutes, and seconds show in the answer.

Example:

2/26/04 11:30:00 PM - 2/25/04 11:30:00 PM should equal ??????

Is this do-able???
Click to expand...

If your first date/time is in A1, and the second in A2, then:

=A1 - A2

Format as: d\d h\h m\m s\s

Format/Cells/Number/Custom Type: d\d h\h m\m s\s


--ron
 
Top