Ignoring dates when calculating differences in time
 

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)?

Ignoring dates when calculating differences in time
 

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)


"drew.skis" wrote:

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)?

Ignoring dates when calculating differences in time
 

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

"N harkawat" wrote:

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)


"drew.skis" wrote:

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)?

Ignoring dates when calculating differences in time
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"drew.skis" wrote in message
...
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

"N harkawat" wrote:

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)


"drew.skis" wrote:

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)?

Ignoring dates when calculating differences in time
 

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


"drew.skis" wrote:

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

"N harkawat" wrote:

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)


"drew.skis" wrote:

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)?