Dates and Time Range

oceanwav

New Member
Joined
Jan 19, 2004
Messages
6
Can anyone show me how to adjust the time and date ranges for it to record only 8 hours work time instead of 24 hours? For example, if I want to find out how long someone takes to enter information record into the system from the time it has uploaded, but I don't want Excel to calculate the time between 4:30pm to 7:30am the next day. I don't know how to format the excel spreadsheet so it just calculate it only the 8 hours work day. For example, if the item uploaded at 4:25pm, the end of a work day, but it didn't set up till the next morning at 7:35am, which means it takes someone 10 minutes to enter that information in the system instead of 15 hours. Please advise. I hope my question makes sense.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi,
this excludes weekends but does not take in concideration lunch break so 07:30 to 16:30 is 9 hours. Start and end time must be within working hours. Format cells with formulas as [hh]:mm
Book1
ABCDEF
1Workday07:3016:30
2
3startdatestarttimestopdatestoptimeDuration
420-jan-0407:3022-jan-0416:3027:00
520-jan-0408:0020-jan-0408:1000:10
620-jan-0412:0021-jan-0415:0012:00
720-jan-0416:0021-jan-0407:3500:35
816-jan-0407:3019-jan-0416:0017:30
9
Sheet1
 
Upvote 0
Thanks for your help. Your formular is wonderful. I am looking for a different formular. This is the scenerio, if I have a worksheet that people can upload their work all day but the people who entering these information in the sytem only work 8 hours a day, say someone upload their work at 8pm on friday and that information enters in the system at 7:35am on Monday morning. Therefore, it takes only 5 minutes to enter that information. I am looking for a formular that excluding weekend, and after working hours. Your formular excluding only after working hours. With your formular, if you enter the start time that passed 16:30 then it doesn't calculate.
Please advise.
 
Upvote 0
OK, I give it one more try, it became quite a long formula. Here you have to specify also lunch hour, else 7:30 to 16:30 will give you 9 hours. Else I think it does what you want. ( the 0 in B2 must be there)
01_23.xls
ABCDEFGH
1WorkdaystartdatestarttimestopdatestoptimeDuration
2020-jan-0405:0021-jan-0410:0010:30
3Start07:3020-jan-0408:0020-jan-0408:1000:10
4Lunch12:0020-jan-0419:0025-jan-0408:0024:00
5Start13:0020-jan-0421:0021-jan-0407:3500:05
6End16:3023-jan-0415:0026-jan-0412:0006:00
7
Sheet1
 
Upvote 0
Hi,
Thanks for your help again. I couldn't see the last part of your formular. Can you screen shot the last part of the formular please? Thanks so much.
 
Upvote 0
oceanwav said:
Hi,
Thanks for your help again. I couldn't see the last part of your formular. Can you screen shot the last part of the formular please? Thanks so much.

Try just clicking in the formula bar with the formula and scrolling over. it has the full formula in there (just like an XL sheet!)

Good luck.
 
Upvote 0
Hi,

This is the formula in H2 that you then can drag down.

=(NETWORKDAYS(D2,F2))/3-CHOOSE(MATCH(G2,$B$2:$B$6,1),$B$6-$B$3+$B$4-$B$5,$B$6-G2+$B$4-$B$5,$B$6-$B$5,$B$6-G2,0)*(WEEKDAY(F2,2)<6)-CHOOSE(MATCH(E2,$B$2:$B$6,1),0,E2-$B$3,$B$4-$B$3,E2-$B$3+$B$4-$B$5,$B$6-$B$3+$B$4-$B$5)*(WEEKDAY(D2,2)<6)
 
Upvote 0
Hi,
Thanks for your quick response. First of all, I created a mimic worksheet like yours using the formular but for some reason the result is #####. I checked the formular a few times but I didn't see any error. I will paste what I have at the bottom. Can you take a look and help me out? I know I have been taken a lot of your time lately with formulars and things. :p Is it possible for you to explain the lengthy formular that you wrote for me? Do you know if there is any website that I can learn different formulars? Again, thanks for all your help. :p
 
Upvote 0
I forgot to paste the excel worksheet that I did using the lengthy formular.

I didn't know how to get the image in here. This is the formular that used on the worksheet but the result turned out to be ######.
Is there anything that I missed?
=(NETWORKDAYS(D2,F2))/3-CHOOSE(MATCH(G2,$B$2:$B$6,1),$B$6-$B$3+$B$4-$B$5,$B$6-G2+$B$4-$B$5,$B$6-$B$5,$B$6-G2,0)*(WEEKDAY(F2,2)<6)-CHOOSE(MATCH(E2,$B$2:$B$6,1),0,E2-$B$3,$B$4-$B$3,E2-$B$3+$B$4-$B$5,$B$6-$B$3+$B$4-$B$5)*(WEEKDAY(D2,2)<6)[/url]
 
Upvote 0
Hi,

Are your formula cells formatted as [HH]:MM ?

Check again that the sheet is looking like mine maybe with exeption of the dates, they should look as you are used to. Try also formatting the cells with the formulas, as general, to check what you have there. When you say that you get #### as result, i suspect that you get a negative time as result which should indicate that your input dates are wrong.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top