Calculate if 2 times differ by 24 hours?

[Robert Crandal]

So, cell A1 contains a fixed time in the format of "hh:mm:ss",
and cell A2 contains a fixed data in the format of "mm/dd/yy".
Users will be entering a time into cell B1 and a date into
cell B2.

How can I determine if the times and dates entered into
cells B1 and B2 are greater than the fixed time and date
in cell A1 and A2 by more than 24 hours?

I hope that makes sense. I'm just looking for code to
do the calculation.

Thank you!

Robert

 

[joeu2004]

So, cell A1 contains a fixed time in the format of "hh:mm:ss",
and cell A2 contains a fixed data in the format of "mm/dd/yy".
Users will be entering a time into cell B1 and a date into cell B2.

How can I determine if the times and dates entered into
cells B1 and B2 are greater than the fixed time and date
in cell A1 and A2 by more than 24 hours?

Ostensibly:

=IF(ABS(A1+A2-B1-B2)>1,">24 hr","<=24 hr")

However, because time is stored as a fraction of a day and because
seconds are a very small fraction, you might run afoul of artifacts of
computer binary arithmetic, resulting in either a false positive or a
false negative.

The following will avoid that:

=IF(ROUND(ABS(A1+A2-B1-B2)*86400,0)>86400,">24 hr","<=24 hr")

86400 is the number of seconds in a day.

Dates are stored as integers, namely the number of days since
12/31/1899 normally.

Time is stored as a fraction of a day. So A1+A2 is effectively the m/
d/yyyy h:mm:ss. Likewise for B1+B2.

 

[joeu2004]

=IF(ABS(A1+A2-B1-B2)>1,">24 hr","<=24 hr") [....]
=IF(ROUND(ABS(A1+A2-B1-B2)*86400,0)>86400,">24 hr","<=24 hr")

I'm sorry. I read your subject ("differ by 24 hours") and overlooked
what you wrote in your posting ("B1 and B2 are greater [...] by more
than 24 hours").

For the latter, the formulas should be:

=IF(B1+B2-A1-A2>1,">24 hr","<=24 hr")

=IF(ROUND((B1+B2-A1-A2)*86400,0)>86400,">24 hr","<=24 hr")

 

[Robert Crandal]

I'm sorry if I confused anyone. I am actually interested
if the time (in B1) and the date (in B2) exceeds or is
GREATER than the time (in A1) and date (in A2) by
more than "24 hours".

Thanks for the formulas. I will give them a try!

8)

=IF(ABS(A1+A2-B1-B2)>1,">24 hr","<=24 hr") [....]
=IF(ROUND(ABS(A1+A2-B1-B2)*86400,0)>86400,">24 hr","<=24 hr")

I'm sorry. I read your subject ("differ by 24 hours") and overlooked
what you wrote in your posting ("B1 and B2 are greater [...] by more
than 24 hours").

For the latter, the formulas should be:

=IF(B1+B2-A1-A2>1,">24 hr","<=24 hr")

=IF(ROUND((B1+B2-A1-A2)*86400,0)>86400,">24 hr","<=24 hr")

 

[Robert Crandal]

Great, the first formula works great for checking if the
time and date in B1 and B2 are GREATER than the
time and date in A1 and A2 by 24 hours.

Now I have another question.....

If the time and date in B1 and B2 are GREATER than
the time and date in A1 and A2 by 24 hours, I would
like to tell the user exactly what is the difference between
the times and dates. I want to display a message box
that outputs something like:

"There is a difference of 2 days, 5 hours, 20 mins and 10 seconds"

etc. etc... or something like that.

How would I do that?

Thank you!

=IF(ABS(A1+A2-B1-B2)>1,">24 hr","<=24 hr") [....]
=IF(ROUND(ABS(A1+A2-B1-B2)*86400,0)>86400,">24 hr","<=24 hr")

I'm sorry. I read your subject ("differ by 24 hours") and overlooked
what you wrote in your posting ("B1 and B2 are greater [...] by more
than 24 hours").

For the latter, the formulas should be:

=IF(B1+B2-A1-A2>1,">24 hr","<=24 hr")

=IF(ROUND((B1+B2-A1-A2)*86400,0)>86400,">24 hr","<=24 hr")

 

[GS]

Robert Crandal wrote on 12/28/2010 :

So, cell A1 contains a fixed time in the format of "hh:mm:ss",
and cell A2 contains a fixed data in the format of "mm/dd/yy".
Users will be entering a time into cell B1 and a date into
cell B2.

How can I determine if the times and dates entered into
cells B1 and B2 are greater than the fixed time and date
in cell A1 and A2 by more than 24 hours?

I hope that makes sense. I'm just looking for code to
do the calculation.

Thank you!

Robert

If you subtract the times from each other you will know elapsed time.
If you subtract the dates from each other you will know elapsed days.

Where you will have issue is when the time in B1 is <= the time in A1,
which indicates the elapsed time passed over midnight. In this case you
need to evaluate the two time values as well as the two date values to
accurately determine the elapsed time. Here's the scenarios you'll
encounter:

1. TimeA1 < TimeB1
2. TimeA1 = TimeB1
3. TimeA1 > TimeB1

Additionally, the date values will be one of these scenarios:

1. DateA2 = DateB2
2. DateA2 < DateB2

These only assume that ColA is the duration 'Start', and ColB is the
duration 'Stop'. The total time elapsed between 'Start' and 'Stop' must
factor both time and date values for each. So...

If(Start<Stop) AND DateStop=DateStart
Then (The duration occured on the same day)
CalculationLogic: Stop-Start*24 = Elapsed HH.MM
Example: Start=10:45AM, Stop=11:22PM:
ElapsedTime=12.62Hrs

If(Start=Stop) AND DateStop>DateStart
Then DateStop-DateStart*24 = Elapsed HH.MM
(The duration occured over more than 1 day)
Example: Start=12:00PM, Stop=12:00PM,
DateStart=12/28/2010, DateStop=12/29/2010:
ElapsedTime=24.00Hrs

If(Start>Stop) AND DateStop>DateStart
Then ((Stop+(DateStop-DateStart))-Start)*24 = Elapsed HH.MM
(The duration occured over more than 1 day)
Example: Start=9:00PM, Stop=1:00AM,
DateStart=12/28/2010, DateStop=12/29/2010:
ElapsedTime=4.00Hrs

This, of course, requires that time values are entered correctly for
AM/PM so that the time serials calculate correctly.

 

[joeu2004]

=IF(B1+B2-A1-A2>1,">24 hr","<=24 hr")
=IF(ROUND((B1+B2-A1-A2)*86400,0)>86400,">24 hr","<=24 hr")

I presume these are the formulas you refer to.

Great, the first formula works great for checking if the
time and date in B1 and B2 are GREATER than the
time and date in A1 and A2 by 24 hours.

__Both__ formulas should do that. I was recommending the second
formula to avoid anomalies ("errors") that arise a result of computer
binary arithmetic.

Although you might uncover any such anomalies in your testing, they
might arise eventually. They are a common problem.

If the time and date in B1 and B2 are GREATER than
the time and date in A1 and A2 by 24 hours, I would
like to tell the user exactly what is the difference between
the times and dates.  I want to display a message box
that outputs something like:
"There is a difference of 2 days, 5 hours, 20 mins and 10 seconds" [....]
How would I do that?

Depends on how specific you need to be.

I would suggest that you use Data Validation. In XL2003:

* Select B1:B2.
* Click on Data > Validation. Click on the Settings tab.
* Select Custom from the Allow pull-down menu.
* Enter the following in Formula
=IF(COUNT($A$1:$A$2,$B$1:$B$2)=4,$B$1+$B$2-$A$1-$A$2<=1,TRUE)
* Click on the Input Message, and deselet Show.
* Click on the Error Alert tab, select Show and fill in Style, Title
and Message as desired.

That will not allow you to indicate the specific difference. You
could show the difference in another cell (e.g. B3), and you could use
Conditional Formatting so that the difference appears only when the
error occurs.

Or you could use VBA to display a Msgbox per se.

Tell me what direction you want to go, and I can provide additional
details if you need them.

PS: There might be other Excel features that you could use. If so, I
am not familiar with them. Perhaps someone else can post a follow-up.

 

[joeu2004]

Typo errata....

Although you might uncover any such anomalies in your testing,
they might arise eventually.  They are a common problem.

Should be: "Although you might __not__ uncover [...]".

 

[joeu2004]

Errata....

=IF(B1+B2-A1-A2>1,">24 hr","<=24 hr")
=IF(ROUND((B1+B2-A1-A2)*86400,0)>86400,">24 hr","<=24 hr")

[....]
__Both__ formulas should do that.  I was recommending the second
formula to avoid anomalies ("errors") that arise a result of computer
binary arithmetic.

Although you might [not] uncover any such anomalies in your testing,
they might arise eventually.  They are a common problem.

On second thought, the second formula (with ROUND) should not be
necessary __if__ the times in A1 and B1 are __constants__, not
computed by formulas, which seems to be true in your case.

I have helped so many people whose formulas behave unexpectedly due to
infinitesimal anomalies arising from computer binary arithmetic that,
arguably, I have become unduly sensitive to the problem, and I over-
reacted.

Just to be sure, I did run through all 86400 seconds in a day,
comparing with 24hr + 1sec, and I confirmed that the two formulas
always agree.

That should not be surprising considering how large 1 second,
represented as a fraction of a day, is compared to the precision of
time with a current date. But when date/time is computed, computer
arithmetic anomalies can still arise, I believe, in part because the
precision of time with a current date is less than the precision of
time alone.

If that's too confusing, simply disregard it. I am merely trying to
justify my concern, which seems misplaced when time is a
__constant__.

Much ado about nothing.