Excel - IF Function Problem - Expert Solution

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

Need help in setting up a Excel formulae to calculate an employee shift timing for allowance. I have employee start date and time & End date and time. I have a time slab to fit in, if an associate work between that hours I should be paying him( for 2 hrs. X amt. for 5 hrs. X Amt etc...)
Solved by F. C. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 01/06/2018 - 12:29
Hi
Excelchat Expert 01/06/2018 - 12:29
Hello how are you?
User 01/06/2018 - 12:29
Good hw are you...
Excelchat Expert 01/06/2018 - 12:29
I'm good, thanks for asking.
User 01/06/2018 - 12:30
I need some help on MS Excel formulae...hope you will be able to assist me with your expertise
Excelchat Expert 01/06/2018 - 12:30
Anyway, is it possible for you to share your worksheet with me so I could better visualize your data and how your requirement comes into the picture?
User 01/06/2018 - 12:31
since I have restrictions...I am unable to share my screen my from here....
User 01/06/2018 - 12:31
but I can explain it better to your...
User 01/06/2018 - 12:31
Need help in setting up a Excel formulae to calculate an employee shift timing for allowance. I have employee start date and time & End date and time. I have a time slab to fit in, if an associate work between that hours I should be paying him( for 2 hrs. X amt. for 5 hrs. X Amt etc...)
Excelchat Expert 01/06/2018 - 12:32
Okay, I understand. Are you able to see the document preview on the right?
User 01/06/2018 - 12:32
this is my ask..Input value will be start Date/Time and End Date/Time..Output value should be an Alphabet if it satisfy our criteria...
User 01/06/2018 - 12:32
Even that I would not be able to view it....
User 01/06/2018 - 12:32
I will quote one Example to you....
Excelchat Expert 01/06/2018 - 12:32
Oh I see, how about send the file as an attachment?
Excelchat Expert 01/06/2018 - 12:32
That would be awesome. thanks
User 01/06/2018 - 12:33
Input Value
User 01/06/2018 - 12:33
Start Date and Time 5/1/2018 6:41:15 AM End Date and Time 5/1/2018 4:07:26 PM
User 01/06/2018 - 12:33
Output value should be based on the below criterias...
User 01/06/2018 - 12:33
Category A - Min 2 Hours between 10 PM to 8 AM Category B - Min 4 Hours between 10 PM to 8 AM Category C - Min 6 Hours between 10 PM to 8 AM
User 01/06/2018 - 12:33
I want to know which Category he belongs to
User 01/06/2018 - 12:33
I need to get output as A B or C based on the Start time and End time
Excelchat Expert 01/06/2018 - 12:34
I see, i'll try to work on it based on the example provided. I'll send to you the file as an attachment. Sounds good?
User 01/06/2018 - 12:34
sure...that would be great :)
User 01/06/2018 - 12:35
I tried this formulae...see this helps us in some way...
User 01/06/2018 - 12:35
=IF(HOUR(A2)>=22,IF((B2-A2)*24<=2,"A",IF(AND((B2-A2)*24>2,(B2-A2)*24<=4),"B","C")))
Excelchat Expert 01/06/2018 - 12:36
Thanks... please give me a few mins and I'll get back to you.
User 01/06/2018 - 12:36
sure...
Excelchat Expert 01/06/2018 - 12:38
Hi I'd like to clarify your category description
User 01/06/2018 - 12:38
sure...
Excelchat Expert 01/06/2018 - 12:39
it says between 10PM to 8AM
Excelchat Expert 01/06/2018 - 12:39
does that mean in your example that i will be zero hours since the employee clocked in at 6:41 AM and clocked out at 4:07 PM
User 01/06/2018 - 12:40
correct ...say for example...I am working between 5PM to 2 AM shift...and If I have completed that shift...for that particular day...I will be qualified for Category B...because I was in shift between 10 PM to 2 AM which is 4 hours...
Excelchat Expert 01/06/2018 - 12:42
Got it. So if it doesn't qualify to these categories, should it return like "N/A"?
Excelchat Expert 01/06/2018 - 12:42
Got it. So if it doesn't qualify to these categories, should it return like "N/A"?
User 01/06/2018 - 12:43
yes in my first example...he will not qualify for any category because....his login time is at 6:40 and when I calculate between my criteria...he spent only 1 hr. 15 mins...and I require them to qualify min. of 2 hrs. between 8 AM
User 01/06/2018 - 12:45
*between 10PM to 8 AM
Excelchat Expert 01/06/2018 - 12:45
Alright, thanks for the clarification
Excelchat Expert 01/06/2018 - 12:45
By the way please feel free to extend the session
User 01/06/2018 - 12:46
sure...
Excelchat Expert 01/06/2018 - 12:46
That is for free :) We are allowed 2 free 20-mins extensions per session
User 01/06/2018 - 12:46
Done...
Excelchat Expert 01/06/2018 - 12:46
Thanks! I'll get back to work now :)
Excelchat Expert 01/06/2018 - 12:57
Hi I'm sorry this is taking longer than I expected. Are you still with me?
User 01/06/2018 - 12:57
yeap...I am on
Excelchat Expert 01/06/2018 - 12:57
Thanks, appreciate you patience. :)
Excelchat Expert 01/06/2018 - 12:58
Time and date formulae are a bit tricky. I'll get back to you with updates from time to time
User 01/06/2018 - 12:58
I was breaking my heads for last two days...and finally landed here for help :)
Excelchat Expert 01/06/2018 - 01:07
Hi! Please see attached for updates
User 01/06/2018 - 01:07
I have extended it again...
User 01/06/2018 - 01:08
oh great...have you got the logic?
User 01/06/2018 - 01:08
I did not receive any file??
Excelchat Expert 01/06/2018 - 01:08
[Uploaded an Excel file]
Excelchat Expert 01/06/2018 - 01:09
Hi! Are you able to see the file? I'm still in the process of fine-tuning the data for row 3. Row 4 should be good to go. Could you confirm?
User 01/06/2018 - 01:09
I got the file and testing it....
Excelchat Expert 01/06/2018 - 01:15
Hi! I'm making good progress with fine-tuning row 3 scenario
Excelchat Expert 01/06/2018 - 01:15
will get back to you with the updated file in 5 mins or so
User 01/06/2018 - 01:16
Super...Awesome...
Excelchat Expert 01/06/2018 - 01:16
Were you able to check row 4?
User 01/06/2018 - 01:17
Can you try this one...
User 01/06/2018 - 01:17
start Time 5/8/2018 4:37:22 AM
User 01/06/2018 - 01:17
End Time 5/8/2018 4:02:40 PM
Excelchat Expert 01/06/2018 - 01:17
Sure :)
User 01/06/2018 - 01:18
I applied the formulae and it's showing as #N/A but they qualify for 2 hrs category right?....
Excelchat Expert 01/06/2018 - 01:20
Testing it now
User 01/06/2018 - 01:20
Ok
User 01/06/2018 - 01:21
and also Start 5/13/2018 0:40 End 5/13/2018 8:43 showing N/A but he spent 8 hrs. between 10 PM and 8AM so he should be under C
Excelchat Expert 01/06/2018 - 01:25
I see, let me try fix that out too.
Excelchat Expert 01/06/2018 - 01:25
I have to formulae set that are now working 1 for rows 3&4, and another for row 5. Trying to integrate them now.
Excelchat Expert 01/06/2018 - 01:26
Please see attached
[Uploaded an Excel file]
Excelchat Expert 01/06/2018 - 01:26
If in case, I am not able to finish this in 3 mins...
Excelchat Expert 01/06/2018 - 01:26
Is it okay if I have the corrected file sent to you via customer support?
Excelchat Expert 01/06/2018 - 01:27
That is through your registered e-mail
User 01/06/2018 - 01:27
sure...
Excelchat Expert 01/06/2018 - 01:27
Thanks!
Excelchat Expert 01/06/2018 - 01:28
We only have 1 min left. I'll try to, but should the session automatically ends, I'll continue working on the file and send it to customer support for them to forward it to your registered e-mail.
Excelchat Expert 01/06/2018 - 01:28
Again, thank you so much for patronizing Excel Chat! :)
Excelchat Expert 01/06/2018 - 01:29
Also, I'd appreciate if you could drop a few lines for your kind and honest feedback after this session. Any constructive criticism or points for improvement are also welcome. Many thanks and have a good day!

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.