(Excel) IF statement for checking and printing certain times

[RumSwift]

Hey,

 

Working on a spreadsheet for use at work for hourly checks of specific tasks. Essentially I'm having an issue writing an if statement to check times and then print another time. Bad explanation, but I'll write it out for you:

 

There are two possible shift start times; 09:30 and 11:45. From there we have 8 checks.

 

If we start at 09:30 the checks look like this:

• 10:30, 11:30 12:30, 13:30, 15:00, 15:30, 16:30 and 17:30

If we start at 11:45 the checks look like this:

• 12:45, 13:45, 14:45, 15:30, 17:00. 17:45, 18:45 and 19:45

The checks I've highlighted are the checks that are done as a lunch break starts. Lunch is 45 minutes and then the next check after 90 minutes after (13:30 - 15:00 // 15:30 - 17:00).

 

Basically what I'm trying to do is write an if statement that checks the time before the lunch break check and then if that time is 12:30, it prints 13:30 for the dinner check and if it's 14:45 it prints 15:30 for the lunch break check. Having some trouble getting it to work. Just wondering if anyone could help me out.

 

Cells are formatted as custom and look like this:

 

Spoiler

 

If this is really unclear let me know and I'll try to explain better. Should just be a simple check but I just can't get it to work .

[Dujith]

Do you want the Check 1 - 8 to be filled in depending on what the shift start is?

[RumSwift]

1 minute ago, Dujith said:

Do you want the Check 1 - 8 to be filled in depending on what the shift start is?

Yeah, sorry, should have outlined that part. 

 

Just using a simple +1/24 to add an hour to each time along the way so far. But if there is an easier way to do it tat would be cool.

[Nocte]

Assuming there are only 2 shift start times and you want the cells changing depending on the content of the cell in the column D you can do:

=IF(D3="09:30:00","10:30:00","12:45:00")

EDIT: That is an example for Check 1.

[Dujith]

5 minutes ago, Nocte said:

Assuming there are only 2 shift start times and you want the cells changing depending on the content of the cell in the column D you can do:

=IF(D3="09:30:00","10:30:00","12:45:00")

EDIT: That is an example for Check 1.

For copying ease add the dollar sign (that locks a letter or number of the row)

=IF($D3

That way it will always check column D and whatever row you are on

[RumSwift]

8 minutes ago, Nocte said:

Assuming there are only 2 shift start times and you want the cells changing depending on the content of the cell in the column D you can do:

=IF(D3="09:30:00","10:30:00","12:45:00")

EDIT: That is an example for Check 1.

See this is what I've been doing, but for some reason it's not printing correctly. 

[Dujith]

6 minutes ago, Rhyss said:

See this is what I've been doing, but for some reason it's not printing correctly. 

What happens? can u show an example?

 

Nvm its Excel converting the cell to a time cell, just tested it.

The correct IF statement is:

=IF($D3=TIME(9;30;00);"10:30";"12:45")

Just remember to use semicolons in the TIME bracket   hours;minutes;seconds

[RumSwift]

13 minutes ago, Dujith said:

What happens? can u show an example?

 

Nvm its Excel converting the cell to a time cell, just tested it.

The correct IF statement is:

=IF($D3=TIME(9;30;00);"10:30";"12:45")

Just remember to use semicolons in the TIME bracket   hours;minutes;seconds

Sorry had to shoot and do something.

 

Thanks so much for this!