Ignoring dates when calculating differences in time

  • Thread starter Thread starter drew.skis
  • Start date Start date
D

drew.skis

How do I identify whether a certain time of day, regardless of date, is
earlier than a fixed time of day (e.g. 11AM - date is not important)?
 
Say your date+time is in cell A1 and you want to compare it with data+time on
cell B1
something like this:
=if(mod(a1,1)>mod(b1,1)...etc)
 
Thank you so very, very, very much! I would have never guessed.

I would like to learn more about using dates and times. Can you recommend a
resource (websites, online classes, books, etc.)? I'm already a fairly
skilled user, but want to know more about this particular area.

At the risk of taking advantage of your kindness, I have one more question:
how do I identify whether a given time is between two times? Same issue as
before - I have many dates and times involved, but the "between times" are
not date sensitive.

I will propose a toast to you, whoever and whereever you are, when my wife
and I do cocktails after works tonight!

Andrew
Seattle, WA
 
Date and time are stored in Excel as serial numbers with 1/1/1900 as zero
So today is 39869. Type =TODAY() in a cell and format it as General to see
this
My local time is about 2:26 PM, if I type =NOW() in a cell (say G26) and
format it general I see 39869.60186
The formula =MOD(G26,1) returns the fractional part: 0.60186.
If I multiply this by 24 (there are 24 hours in a day) I get 14.48113 which
is about 14 ½ hours (as I said it was 2:26 when I started this)
If I have =G26*24 in G27 and I format this as time I see 2:26:45 PM
Hope this helps you get the idea
Chip tells more at http://www.cpearson.com/excel/datetime.htm
best wishes
 
Simply modifying the formula something like this
=IF(AND(MOD(D10,1)<MOD(D9,1),MOD(D10,1)>MOD(D11,1)),"do this","etcet")

basically if D9 is the upper bound and D11 lower bound and D10 is the value
you wish to compare
 
Back
Top