View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default Calculate if 2 times differ by 24 hours?

On Dec 27, 11:46 pm, joeu2004 wrote:
=IF(B1+B2-A1-A21,"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.


On Dec 28, 1:06*pm, "Robert Crandal" wrote:
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.