IF statemetent refering to date and time

[hyperx]

Hello,

I'm wanting to automatically fill a cell depending on the date and time of two other cells.

These are the statements I'm trying to formulate into one cell:

1. Mon-Thurs, 08:01-17:00 = "A"
2. Mon-Thurs, 17:01-08:00 = "B"
3. Fri, 08:01-13:00 = "A"
4. Fri, 13:01 - Sun, 17:00 = "C"

I currently have a column with the date in, a column which reads the date and returns the day name (ie, A3=04/09/2017, A4=Mon) and a column with the time.

Any help much appreciated!

Many thanks

[cbatrody]

Hi,

Welcome to the forum.

Try the following formula in D1:

=IF(OR(WEEKDAY(A2,1)=6,WEEKDAY(A2,1)=7,AND(WEEKDAY(A2,1)=5,C2>13/24)),"C",IF(OR(AND(WEEKDAY(A2,1)<=4,C2>8/24,C2<=17/24),AND(WEEKDAY(A2,1)=5,C2>8/24,C2<=13/24)),"A","B"))

see the attached file.

[Aardigspook]

Welcome to the forum.

You can do this with a combination of IF, AND and OR using WEEKDAY(cell) and checks such as >=17/24 for time. However, the list you have above doesn't cover all the possibilities - what do you want for Fri before 08:00, for example? or Monday before 08:00? or Sunday after 17:00?

The formula might start something like this, which will give you the 'A' for the Mon-Thu period:

Please Login or Register  to view this content.

This assumes date in col A and time in col C - note that you don't need the separate day name column.

Hope that helps.

If you could say what you want for the other possibilities, we can build the rest for you as well.

[hyperx]

Ideal, thank you both!

I've used both methods and slightly adapted them to suit but otherwise problem solved!

Many thanks

[Aardigspook]

Glad we were able to help and thanks for marking the thread as Solved.