-5

How can I calculate hours worked on a project using specific working hours that aren't the same each day?

So Monday - Friday I work 7 am-7 pm, Saturday 9 am -1 pm and I take Sunday off (lucky me). If i start a project on the 1st March 10 am and finish on the 5th March at 9 am how can I calculate an answer of 27 hours ??

I have two cells date/time start and date/time finish. I have multiple rows to do this to and several time points but this essentially will work the same.

I hope makes sense.

Edit - Solutions tried and opposing results enter image description here

Community
  • 1
  • 1
grahamie
  • 301
  • 2
  • 6
  • 15
  • Do you have your schedule in the spreadsheet? – cybernetic.nomad Mar 07 '18 at 21:25
  • You should search on here before posting - there are several questions about this type of problem already answered... – Solar Mike Mar 07 '18 at 21:27
  • Schedule can be in a spreadsheet if needed. Can be on a separate tab – grahamie Mar 07 '18 at 21:29
  • SOLAR MIKE - i did search. could not find the answer. if you know where it is it would be helpful if you point me there please – grahamie Mar 07 '18 at 21:30
  • 1
    One : https://stackoverflow.com/q/49108195/4961700 , Two : https://stackoverflow.com/q/46214721/4961700 , Three : https://stackoverflow.com/q/21276792/4961700 , any of these may give you either the answer you need or a hint... Didn't take long to find... – Solar Mike Mar 07 '18 at 21:41
  • 1
    And don't shout at me... – Solar Mike Mar 07 '18 at 21:41
  • Apologies Solar Mike Shouting unintentional. Although 3 examples are given, not one actually answers my question. I could also give you links to posts that Aren't the answer. – grahamie Mar 07 '18 at 21:49
  • 4
    you give no data, no code, no formulas, no references to research..... links to post that aren't the answer would be at least 1 thing. – QHarr Mar 07 '18 at 21:51
  • I don't have formulas to show (this is the help i am looking for) and the data is in the description – grahamie Mar 07 '18 at 21:52
  • 1
    to which I respond [ask] – QHarr Mar 07 '18 at 21:53
  • to which i respond Thanks....... i'll know for next time. – grahamie Mar 07 '18 at 21:55
  • @grahamie - **Welcome to [so]!** You might have the wrong idea about this site; [so] is a place for professional *(or enthusiast)* programmers to share advice about ***unique*** software development problems whose solution can't be found elsewhere. S.O. Questions should have source code in them or at least cover a ***specific programming problem***, algorithm, or software tool. **You're seeking a beginner's lesson in basic spreadsheet operation**; there are other sites & tutorials online more suited to your need. Please see "[help/on-topic]" as well as "[ask]". – ashleedawg Mar 07 '18 at 22:02
  • @grahamie Here's a video tutorial from Microsoft that will help you find the answers that you're seeking: [Learn the basics of Excel formulas with videos on YouTube](https://support.office.com/en-us/article/learn-the-basics-of-excel-formulas-with-excelisfun-videos-on-youtube-13e2154a-cec9-42ca-adbc-0d470f6fe3be?CorrelationId=78c2bd7a-2e19-4ee4-9528-76f2b04736c5). To start your formula off, you can calculate hours between two times like `( [End DateTime] - [Start DateTime] ) * 24` ***Good luck!*** – ashleedawg Mar 07 '18 at 22:02
  • Thankyou Ashleedawg i will watch and hopefully it will help. i can already calculate the hours between two date/times but need to remove the "out of hours" times. i have searched and searched for an answer to this issue everywhere which is why i have tried to find help here. i dont feel this is a basic spreadsheet operation – grahamie Mar 07 '18 at 22:05
  • Possible duplicate of [Calculate Working days and time between two days in excel](https://stackoverflow.com/questions/21276792/calculate-working-days-and-time-between-two-days-in-excel) – Foxfire And Burns And Burns Mar 07 '18 at 22:13
  • no quite, i need to include Saturday as a partial work day – grahamie Mar 07 '18 at 22:15
  • 1
    Then you need to calculate how many whole-Saturdays between the dates, and multiply that by 4 hours. [This may help](https://stackoverflow.com/questions/36740329/how-to-calculate-total-fridays-between-two-dates-in-excel-on-weekly-biweekly-an?rq=1) – Chronocidal Mar 07 '18 at 22:18

1 Answers1

2

You will need a helper column with this formula:

=24*(SUMPRODUCT((TEXT(ROW(INDEX(AAA:AAA,$F$1):INDEX(AAA:AAA,$F$2)),"dddd")=A1)*(C1-B1))-IF(TEXT($F$1,"dddd")=A1,MOD($F$1,1)-B1,0)-IF(TEXT($F$2,"dddd")=A1,C1-MOD($F$2,1),0))

Then sum that column.

enter image description here

Here it is in one formula using NETWORKDAYS.INTL

=IF(DATEDIF(F1,F2,"d")>1,NETWORKDAYS.INTL(F1+1,F2-1,"0000011")*12+NETWORKDAYS.INTL(F1+1,F2-1,"1111101")*4,0)+IF(DATEDIF(F1,F2,"d")>0,(MOD(F2,1)-IF(WEEKDAY(F2,2)<6,TIME(7,0,0),TIME(9,0,0)))*24+(IF(WEEKDAY(F1,2)<6,TIME(19,0,0),TIME(13,0,0))-MOD(F1,1))*24,(F2-F1)*24)

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81