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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
 

oceanwav

New Member
Joined
Jan 19, 2004
Messages
6
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.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
 

oceanwav

New Member
Joined
Jan 19, 2004
Messages
6

ADVERTISEMENT

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.
 

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
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.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

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)
 

oceanwav

New Member
Joined
Jan 19, 2004
Messages
6
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
 

oceanwav

New Member
Joined
Jan 19, 2004
Messages
6
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]
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,675
Members
425,229
Latest member
Rashid mahmood

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
Top