Midnight as TIME()

I have two conditional formulas that check to see if an employee is a minor, and depending on the day of the week, checks to make sure they haven't been scheduled past 10pm or midnight.

The 10:00 check:

=IF(AND(AS7="yes",F7>TIME(22,0,0)),"yes","no") works as intended, where AS7 checks their age.

The midnight check is not going as well. I would have guessed replacing the 22 with a 24 would do the trick, but it is not.

=IF(AND(AS7="yes",F7>TIME(24,0,0)),"yes","no")

If a minor is scheduled past midnight, it still comes up as "no". I have tried TIME(0,0,0), and that does not work. I can put in TIME(23,59,59), but if the employee is scheduled at midnight, that does not violate any condition of employment, but the result is "yes" which is correct for the formula, but not what I am looking for.

Thoughts?

Try:

=IF(AND(AS7="yes",F7>=TIME(0,0,0)),"yes","no")


If you're checking for midnight or beyond (infinity and beyond ...), you need greater than or equals.


Regards

The issue I foresee with the above suggestion of:

Please Login or Register  to view this content.

is that the result will always be "yes".

If we make some assumptions regards longevity of shift (ie < 24 hours) I suspect you really want to check if the end time precedes the start time (ie 18:00 to 04:00) ?

If for sake of example we assume start of shift is in E7 then:

Please Login or Register  to view this content.

If you wished to combine the checks (ie >= 2200 inclusive of post midnight) then:

Please Login or Register  to view this content.

The above assumes start/end times are time only (i.e. do not include dates) - based on earlier comment of:

Originally Posted by ZooTV92

=IF(AND(AS7="yes",F7>TIME(24,0,0)),"yes","no")

it still comes up as "no"

were values datetime then the result would be "yes".

Originally Posted by ZooTV92

If a minor is scheduled past midnight, it still comes up as "no". I have tried TIME(0,0,0), and that does not work. I can put in TIME(23,59,59), but if the employee is scheduled at midnight, that does not violate any condition of employment, but the result is "yes" which is correct for the formula, but not what I am looking for.

Thoughts?

Hi
I guess you would not be expecting a minor to be working before 6:00 AM, so maybe you could just use
=IF(AND(AS7="yes",F7<TIME(6,0,0)),"yes","no")

The above suggestion fails if the employee is schedule at midnight.

DonkeyOte's suggestion works perfectly, but it exposed another issue I have.

Although cosmetic, I have a name ranged list I use for the times. The first time in the list begins at 7am, and goes every 15 minutes through 3am. At first this suggestion failed. But I noticed that at 12am, it worked, but at 12:15am it did not. What was different was that for all the times from 12:15am to 3am, they were preceded by the date 1/1/1900 (in the formula bar - not the actual list). For S's and giggles, I re-configured the list of times to begin at 12am, every 15 minutes up until 11:45pm, and everything worked as it should because the date was no longer there for any of the times.

It's not the end of the world, but is there a way to make that date go away? All of the cells are custom configured as h:mm AM/PM.....no dates, so I am uncertain why it is showing up at all?? I would like the list to begin at 7am, since that is generally around the start of our day. The list just doesn't look right when it begins at 12am, plus I have to include times in the morning I will never use.

Thanks again for all your suggestions.

In XL Dates are Integers (1 = 24 hours) and Time is Decimal (12:00 = 0.5). In reality Date Time values are simply Numbers it is via Formatting that these numbers "appear" as Dates or Times (or both).

18:00 = 0.75 (3/4 of 24 hours)

18:00 + 12:00 = 1.25 (1 1/4 days).

On the 1900 Date System (Windows) Day 1 is 1st Jan 1900.

1.25 = 1/1/1900 06:00

If you want to only ever include Time you need to remove the Integer and leave only the Decimal and this you can do a number of ways:

Please Login or Register  to view this content.