Net work hours Calculation
Hi, i have a similar question and mentioned below
Start date in F2 Column 5/13/2024 3:36:32 AM
End date in G2 Column 5/15/2024 10:38:40 AM
from start & end date time calculate the working hours from start shift day is 8pm in AX3 column to End shift day is 7am AY3 Column by excluding the weekend and holiday range AU2:AU12 need a excel formula. Thanks
Are you still using Excel 2016?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
You already have formula:
=NETWORKDAYS.INTL(F2,G2,1,Holidays)-(NETWORKDAYS.INTL(F2,F2,1,Holidays)*IF(MOD(F2,1)>ET,1,(MAX(ST,MOD(F2,1))-ST)/(ET-ST))-(NETWORKDAYS.INTL(G2,G2,1,Holidays)*IF(MOD(G2,1)<ST,1,(ET-MIN(ET,MOD(G2,1)))/(ET-ST))))*(ET-ST)*24
If this isn't working for you, tell us in what way. What is your expected result (manually calculated)?
Hi, now i am using O365 version and manual calculation is below
6th Day 7th Day 8th Day output should be 22 hours
20.00 to 00.00 00.00 to 7.00 20 to 00 00 to 7 22.00
From 6th day 20.00 to next day 7.00 is 11 Hours
From 7th day 20.00 to next day 7.00 is 11 Hours
Total 22 hours
I required a formula to calculate from PM to AM
Last edited by Saikeyan; 05-21-2024 at 10:24 AM.
OK - so first, please update your formum profile from 2016 to 365.
Yes i have updated my profile
Last edited by Saikeyan; 05-21-2024 at 10:44 AM.
Any updates on the query
It would be helpful if you can give more examples.
Sure I can provide you
Day 1 if the user logged at 8pm and they logged off at 7am next day then the work hour is 11
Day 2 if the user logged at 5pm and they logged off at 8am next day then the work hour is 11
Since the shift hours will start at 8pm and end in 7am in morning
if I have sum both day hours it should be 22 hrs.
Note - we have to exclude the weekend and holidays for the start to end date
The shift starts at 8 PM and end at 7 AM next day.
so when the shift starts at 8PM Friday it ends at 7 AM on Saturday Morning.
shift starts at 8 PM on Monday and end at 7AM the next day.
Please provide a sample workbook with your examples. It would be helpful to provide at least 5 to 10 examples.
Yes, and provide samples that INCLUDE weekends and holidays. Make sure that not all rows have to add up to 11 hours, as that is not a good sample.
I have provided the samples in the workbook; please take a look.
Last edited by Saikeyan; 05-23-2024 at 09:51 AM.
Try this, =(MAX(NETWORKDAYS.INTL(INT(F2)+1,INT(G2)-1,1,Holidays),0)*MOD(ET-ST,1)+MOD(IF(INT(F2)=INT(G2),ST,IF(MOD(G2,1)>ET,MIN(MOD(G2,1),ET),MAX(MOD(G2,1),ET)))-MAX(MOD(F2,1),ST),1)+IF(MOD(G2,1)>ST,MOD(G2,1)-ST,0))*24
Hi Joseph,
the formula is working and Its really helpful, thanks you so much
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Thanks for the feedback & Rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks