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
How may I help you today?
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
no problem, give me an example. I will help you out
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
what about dinner?
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.
