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.
