PDA

View Full Version : Calculate difference between working hours and sometimes dates - Formula Please



ashleyuk1984
09-09-2015, 01:37 PM
Hi,
I'm in desperate need of a formula.
I've searched around on Google and I've found plenty of tutorials on this, but none of them seem to be just for 'working hours'... Some of them go far too far into depth, and perhaps I'm just feeling a little overwhelmed with all of the information that I'm trying to take in.

This is what I have...

http://www.ultraimg.com/images/2015-09-09_21-24-54.jpg

The overall end goal is to show how long jobs took us to complete (within working hours).
These results are collected at the end of the month, and a graph is made showing how many jobs took us under an hour
How many took us between 1 to 2 hours.
How many took us between 2 to 3 hours and then more than 3 hours.

At the moment, we are manually calculating this and it's taking quite some time. :banghead:
It just seems silly to manually do this, when surely a formula will accomplish this for us in seconds.

Can any one help with this please?

ashleyuk1984
09-09-2015, 04:47 PM
Ahhh, ok so I eventually found something that looks like what I want.

http://www.ultraimg.com/images/2015-09-10_00-39-36.jpg

This takes into account weekends and the option of national holidays, which is good.
Currently the formula calculates from 8:00 am in the morning, to 17:00 pm.

I need the formula to look at 9:00 to 17:00 ... But the formula is so complex that I simply can't find the correct place to change the formula without screwing it totally up.

Could anyone help with this formula?
Which parts do I need to change to alter both the start time and end time (if I ever need to do that)?

Thanks

Teeroy
09-09-2015, 11:28 PM
Nice Formula. The existing formula limits the number of hours per day to 9 hours, and starts from 8am. You want to limit the number of hours per day to 8 hours, starting from 9am. In essence you need to swap the positions of 8 and 9 in the current formula.

Try, as an Array formula:

=(((SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=6)+(WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),1)=7),0,1))-SUMPRODUCT(IF(IFERROR(MATCH(ROW(INDIRECT(INT(A2)&":"&INT(B2))),Holiday,0),0),1,0)))*8)-(IF(INT(B2)=IFERROR(INDEX(Holiday,MATCH(INT(B2),Holiday,0)),0),0,((WEEKDAY( B2,1)<6)*MAX(0,MIN(8,17-MOD(B2,1)*24)))))-(IF(INT(A2)=IFERROR(INDEX(Holiday,MATCH(INT(A2),Holiday,0)),0),0,((WEEKDAY( A2,1)<6)*MAX(0,MIN(8,MOD(A2,1)*24-9))))))*60

ashleyuk1984
09-10-2015, 12:00 AM
Hi Teeroy,
Thank you very much for that. I've tested it, and it appears to be working. :bow:

--- EDIT ---

Seems like I found a couple of errors in the formula, it was calculating Sunday to Thursday. (excluding Friday and Saturday)
I needed it to calculate Monday to Friday. Seems like I have fixed that now.

So, now it's calculating Monday to Friday 09:00 till 17:00 (excluding weekends and public holidays).
Which is exactly what I needed, however I forgot that we actually count Saturday's from 09:00 till 12:00 as working hours :crying:

I need to add Saturdays to the formula, but only from 09:00 to 12:00

Is this possible??

(I have uploaded my latest workbook showing the current working formula.)

Thank You.

Teeroy
09-10-2015, 05:37 AM
So the first fix is easy, and it sounds like you may have it. Change ",1" to ",2" in the 1st two Weekday function terms of the formula.

The second fix is possible, but a little harder, and will make the formula quite a bit longer. I don't have the horsepower left today (it's nearly 11pm) but I'll look at it tomorrow if no-one else answers.

ashleyuk1984
09-10-2015, 07:38 AM
Yeah that's exactly what I done (changed the 1 to 2). Thanks for confirming that bit.
I'm perfectly fine with the formula being a bit longer. Ultimately it does exactly what I'm looking for + it will be absolutely perfect once the Saturday bit is added.

Have a good night!
Thanks for all your help.

Teeroy
09-11-2015, 05:23 AM
Try the following formula; it's quite long-winded but the logic seems to work:

=((((SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(A10)&":"&INT(B10))),2)=6)+(WEEKDAY(ROW(INDIRECT(INT(A10)&":"&INT(B10))),2)=7),0,1))
+(SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(A10)&":"&INT(B10))),2)=6),3/8,0)))
-SUMPRODUCT(IF(IFERROR(MATCH(ROW(INDIRECT(INT(A10)&":"&INT(B10))),Holiday,0),0),1,0)))*8)
-IF(INT(B10)=IFERROR(INDEX(Holiday,MATCH(INT(B10),Holiday,0)),0),0,((WEEKDAY (B10,2)<6)*MAX(0,MIN(8,17-MOD(B10,1)*24))))
-(IF(INT(B10)=IFERROR(INDEX(Holiday,MATCH(INT(B10),Holiday,0)),0),0,((WEEKDA Y(B10,2)=6)*MAX(0,MIN(3,12-MOD(B10,1)*24)))))
-(IF(INT(A10)=IFERROR(INDEX(Holiday,MATCH(INT(A10),Holiday,0)),0),0,((WEEKDA Y(A10,2)<6)*MAX(0,MIN(8,MOD(A10,1)*24-9)))))
-(IF(INT(A10)=IFERROR(INDEX(Holiday,MATCH(INT(A10),Holiday,0)),0),0,((WEEKDA Y(A10,2)=6)*MAX(0,MIN(3,MOD(A10,1)*24-9))))))*60)

Again, enter as an array formula.

I've changed the way your note works as well in the attached file to show you a different method as well as shown the development steps i used for the logic of the above formula.

I hope it helps.

SamT
09-11-2015, 02:59 PM
It would be a lot simpler if a job that covers two days, (or other non-contiguous time periods,) or more was reported on two lines or more. [Consider a 12 hour job that starts at 1200 on a 1/2 day Friday, and finishes after a long weekend.]

With on line per time period:
The job time per day is =FinishTime - StartTime
Total job time is =(Array Formula) SumIf Jobnumber (formula greatly abbreviated)

That would handle break times, overtime, weekends, holidays and even unexpected plant closures, weekend work, and any other times a job may be extended or interrupted for any reason.

Personally, since the two Key Fields of the data are date and job, I would layout the columns: Date; Job; StartTime; FinishTime.

ashleyuk1984
01-01-2016, 04:57 PM
Hi,
Sorry to bring this subject up again.
The solution posted in post number 7 (http://www.vbaexpress.com/forum/showthread.php?53704-Calculate-difference-between-working-hours-and-sometimes-dates-Formula-Please&p=330598&viewfull=1#post330598) worked perfectly. Thank you very much. (It seems that I totally forgot to thank you last time - sorry).

We have decided to move all of our spreadsheets onto Google Sheets.
All of the fields work, apart from this column with this formula.

Unfortunately Google Sheets doesn't like the formula.
So I'll either need to fix the current formula, or re-think a new formula that Google Sheets will accept.

Ideally, I wouldn't like the format of the spreadsheet to change.. This is used by several people, and they'll only complain that it's different to what their used to lol.

Here I will summarise the rules of the formula

The formula should only be looking at our 'working hours'.

We work the following days.
Monday to Friday - From 9:00am to 5:00pm (Lunch breaks count as working hours)
Saturdays - From 9:00am to 12:00pm

Sundays, and Bank Holidays (which are listed on the sheet and refereed too as a named range) should be ignored.

We are assessed from the moment we receive the job, to the moment that particular job is completed.
Sometimes the job can take several days, so that's why we need to account for multiple days.

Our graphs are based on the number of minutes it's taken to complete the job.

I don't mind what the formula looks like, or how to get to the end result, as long as the formula and answer is the expected / correct answer... So if you want to calculate the difference by hours, and then change that into minutes, I'm not fussed.

It's probably worth reading the whole topic from the top and work your way down to this point, but hopefully I've covered everything in this post.

Here is my Google Sheets spreadsheet (in condensed format due to confidentially issues).
This is basically how we layout our main spreadsheet.

https://docs.google.com/spreadsheets/d/1bmVV9KiiDuOmiXabzhxvJrh-AyefZ0ToM1ODZmjHi-I/edit?usp=sharing

You won't be able to edit this version - because obviously I don't want someone deleting it all. But I have allowed comments to be posted.
You'll be able to go to File and Download As (Excel file), if you'd like to edit your own version in Excel.

If you can get the currently formula working again, then great... but if not, and you can think of another solution then that would be awesome.

Thank you for your help
(sorry for the lengthy post)

SamT
01-01-2016, 07:43 PM
https://support.google.com/docs/table/25273?hl=en

ashleyuk1984
01-03-2016, 04:48 PM
Thanks for linking that SamT.

I came across a nice formula today that works well in Google Sheets, and it gives me the number of minutes it takes to complete a job.
The formula only works on Monday to Friday though, so I'm missing 09:00am to 12:00pm on Saturday.

I'm wondering if I was to provide some data, and the expected results, maybe we can get Saturday working.

New formula is:


=SUM(IF(OR($H$20<$G$20,I6<D6),0,(NETWORKDAYS(D6,I6,HolidayList)-(NETWORKDAYS(D6,D6,HolidayList)*IF(MOD(D6,1)>$H$20,1,(MAX($G$20,MOD(D6,1))-$G$20)/($H$20-$G$20)))-(NETWORKDAYS(I6,I6,HolidayList)*IF(MOD(I6,1)<$G$20,1,($H$20-MIN($H$20,MOD(I6,1)))/($H$20-$G$20))))*($H$20-$G$20)*24)/24)*1440

So yeah... you can probably tell why I'm confused.
I'm not too familiar with Networkdays & Mod, so this is quite challenging for me.

As I would like to include Saturday, I imagine the Networkdays function will need to be changed to Networkdays.Intl ?? But, I still don't know how to go about adding the times, and getting the correct criteria.

Anyway, I would greatly appreciate some help on this if someone has some spare time.
Thank You very much.

I have uploaded a new spreadsheet.
Column L contains the new formula that needs Saturday adding.

Again, thank you for any help.

ashleyuk1984
01-06-2016, 01:57 PM
Hi,
Sorry does anyone have any ideas on this?
Please see my previous post above.

Thank you