# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi, I need help with excel formula. It is to with IF formula and allocating time based on range..
Solved by K. L. in 46 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 25/11/2017 - 07:32
Welcome, Thanks for choosing Got It Pro-Excel
Excelchat Expert 25/11/2017 - 07:32
User 25/11/2017 - 07:35
Hi, I am working on rota spreadsheet..and need the times of shifts to display wheteher it is breakfast shift, lunch shift, afternooon shift or dinner shift..
User 25/11/2017 - 07:36
The problem I am facing is that Breakfast is from 6am-12pm and lunch starts at 12pm..so if I put 12pm in the rota it shows both breakfast and lunch allocation..
Excelchat Expert 25/11/2017 - 07:37
ok give me a moment
User 25/11/2017 - 07:37
I will copy the whole spreadsheet
User 25/11/2017 - 07:37
if it helps..
Excelchat Expert 25/11/2017 - 07:39
User 25/11/2017 - 07:48
so I need excel to analyze the data in cells B5 and C5, and D5 and E5. And to put one if they belong to particular shift..
Excelchat Expert 25/11/2017 - 07:49
Can you please mentioned , what value do you need in thats cells so i can put the formula according that
User 25/11/2017 - 07:50
time
User 25/11/2017 - 07:50
value in final cells will be 1 if it is part of the shift, 0 if not..
Excelchat Expert 25/11/2017 - 07:51
in "Breakfast 6-12" what value will be appear?
Excelchat Expert 25/11/2017 - 07:51
Please mention manually i will create formula for that
User 25/11/2017 - 07:52
If b5-c5 or d5-e5 fulfils criteria of breakfast shift, then1 otherwise 0. Bare in mind that breakfast shift is ANY shift between that time..
User 25/11/2017 - 07:53
6-12
User 25/11/2017 - 07:54
so with current values John should appear for breakfast, afternoon and dinner as !
User 25/11/2017 - 07:54
1
User 25/11/2017 - 07:55
TOny should appear 1 as Lunch, afternoon and dinner
Excelchat Expert 25/11/2017 - 07:56
ok, please let me know if I am doing right or wrong
User 25/11/2017 - 07:58
John is not there for lunch, he is missing between 12-15
User 25/11/2017 - 07:59
let me clarify..
Excelchat Expert 25/11/2017 - 07:59
sure
User 25/11/2017 - 08:00
John is on split shift working 6-12 and then 15-22
User 25/11/2017 - 08:00
so he should appear as 0 for lunch
Excelchat Expert 25/11/2017 - 08:00
ok
User 25/11/2017 - 08:01
Tony is on straight shift working 12-22
Excelchat Expert 25/11/2017 - 08:01
got it
User 25/11/2017 - 08:01
thank you :)
User 25/11/2017 - 08:03
My problem is the overlaping times 12, 15, 18 as they appear on both shifts..
User 25/11/2017 - 08:04
even when john is finishing at 12, he still appears on lunch..
User 25/11/2017 - 08:05
so i need excel to recognize the time range more the <>= of psrticular time..
Excelchat Expert 25/11/2017 - 08:08
User 25/11/2017 - 08:09
John should be 1011, Tony 0111
User 25/11/2017 - 08:10
Shall I add more sample shifts to test?
Excelchat Expert 25/11/2017 - 08:10
no its fine
Excelchat Expert 25/11/2017 - 08:16
I am sorry, can you please re-post this question , other expert will help you out, I have technical problem with my system.
Excelchat Expert 25/11/2017 - 08:16
I am really sorry for that
User 25/11/2017 - 08:17
WHere shall I repost it?
Excelchat Expert 25/11/2017 - 08:17
in the same website where did you post it
User 25/11/2017 - 08:17
ok..

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.