Time period of the day - advanced if statement

**stibay** · 10-21-2013, 10:27 AM

Hey.

I am trying to make a pretty advance IF statement and I have just ended up with a big headache trying to understand how IF statements work in Excel.

I am basically trying to say if its during the night (22.00 - 06.59) or during the day (07.00-21:59) so I use the HOUR formula to convert my time to a number between 0 and 23 and this works fine.

However i have a start time (x) and end time (y) so it becomes a bit complicated.

I am basically trying to write this in excel:
//x = HOUR(H2)
//y = HOUR(I2)

IF
(x>=22 and x<=23) or (x>=0 and x<=6)
and
(y>=22 and y<=23) or (y>=0 and y<=6)
//if true write "Night" else "Day"

So if x is between 22 and 23 or 0 and 6 its during the day
but i also want to check the end time is also between these times so I do the same for y.

The problem is when I try to write this in excel.
I have come up with something like:
=IF(OR(AND
(HOUR(H2)>=22;HOUR(H2)<=23);
AND(HOUR(H2)>=0;HOUR(H2)<=6);
OR
(AND(HOUR(I2)>=22;HOUR(I2)<=23);
AND(HOUR(I2)>=0;HOUR(I2)<=6))
);"Night";"Day")

This formula works (amazingly)
But it only check x and y individually and don't compare them. It might be an easy fix from this but my head is exploding so would really appreciate some help. It probably is a way better way of solving this.

Thanks in advance.

**Pauleyb** · 10-21-2013, 11:39 AM

So, it seems like you have a formula that properly calculates the logic you desire, as stated. Then you say 'But it only check x and y individually and don't compare them.' I guess that is the problem, but I don't understand what 'compare them' means and what you want. Please explain.

**stibay** · 10-21-2013, 12:42 PM

Originally Posted by Pauleyb

So, it seems like you have a formula that properly calculates the logic you desire, as stated. Then you say 'But it only check x and y individually and don't compare them.' I guess that is the problem, but I don't understand what 'compare them' means and what you want. Please explain.

Hmm ok, I dont think compare was the right word for it.

But I can try to explain it abit better.

In the database I have 2 columns thats relevant for this if statement, start_time and end_time (x and y)
The data is date format but I have fixed it so I just have the start time and end time in 2 columns (format 00:00 - 23:59)

And with the HOUR() formula i mentioned before I get a number from 0-23 from those times (don't know if this is the best way but seems to work atleast)

So then I want to check if the time is between 22:00 and 06:59 (or 7, doesnt matter that much, but for the HOUR formula 6:59 = 6)
So what I have to check first if its between 22-24, so thats 22 - 23 and then if its from 24:00 to 06:00, so 0-6.

And if that wasnt enough I want to check that for BOTH X and Y (so I wont get records like 23:00 to 15:45 or 03:00 to 16:00 as "Night" f.ex)
So if X is between 22-06 (22-23 and 0-6) AND Y is between 22-06 (22-23 and 0-6) Then i want to write "Night" Else "Day"

Hope this made it abit clearer

**FDibbins** · 10-21-2013, 12:46 PM

Hi and welcome to the forum

To keep things simple, test with actual times (excel sees time as a decimal of 1 day - 12:00 mid-day is actually 0.5 etc), use 7/24 and 22/24
Also, test for the "day-time", and anything else will be night time
=if(and(A1>=7/24,A1<=22/24),"do this","do that")

**stibay** · 10-21-2013, 01:01 PM

Originally Posted by FDibbins

Hi and welcome to the forum

To keep things simple, test with actual times (excel sees time as a decimal of 1 day - 12:00 mid-day is actually 0.5 etc), use 7/24 and 22/24
Also, test for the "day-time", and anything else will be night time
=if(and(A1>=7/24,A1<=22/24),"do this","do that")

Hi.

My statement is only test for "one thing" if not everything else.

The problem is that I want to check both X and Y if its between 22 and 6. (See my last post) So how would you include that in your solution?

**Pauleyb** · 10-21-2013, 02:14 PM

So, you need another AND wrapper around your formula? I also think you have a bug in your formula since you have 3 entries for your first OR statement. Something like this?
=IF(AND(OR(AND(HOUR(H2)>=22,HOUR(H2)<=23),AND(HOUR(H2)>=0,HOUR(H2)<=6)),OR(AND(HOUR(I2)>=22,HOUR(I2)<=23),AND(HOUR(I2)>=0,HOUR(I2)<=6))),"Night","Day")

**stibay** · 10-23-2013, 03:33 AM

Originally Posted by Pauleyb

So, you need another AND wrapper around your formula? I also think you have a bug in your formula since you have 3 entries for your first OR statement. Something like this?
=IF(AND(OR(AND(HOUR(H2)>=22,HOUR(H2)<=23),AND(HOUR(H2)>=0,HOUR(H2)<=6)),OR(AND(HOUR(I2)>=22,HOUR(I2)<=23),AND(HOUR(I2)>=0,HOUR(I2)<=6))),"Night","Day")

Thanks alot

That is working perfect

Time period of the day - advanced if statement

LinkBack

Thread Tools

Rate This Thread

Display

Time period of the day - advanced if statement

Re: Time period of the day - advanced if statement

Re: Time period of the day - advanced if statement

Re: Time period of the day - advanced if statement

Re: Time period of the day - advanced if statement

Re: Time period of the day - advanced if statement

Re: Time period of the day - advanced if statement

Thread Information

Users Browsing this Thread

Similar Threads

Identify if a date/time period exists within a calender period date and time

[SOLVED] Help taking a time from one field and outputting a time period in a different column

[SOLVED] Complicated:Calculate time period based on no. of units sold + fluctuations within period

Specific time period calculation from a time range

Trying to understand a formula to calculate elapsed time in a specific time period

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions