Calculating work hours with additional needs

[newbienewb]

Hi guys! I'm new here so hello

I'm a complete newbie when it comes to excel, trying to figure out a way to make some of my tedious work automated...

I need a formula/macro (?) which calculates work hours wih night shifts and couple more oddities, found some examples for calculating night shifts but I've got no idea how to edit them into something that I need.

It could be 5 formulas as well, each for one output cell, since first one is solved with a simple =(B1-A1)*24

For input, I've got two cells - one has start date & hour and second has finish date & hour, both in yy-mm-dd hh:mm format

I need to calculate total work hours, divided between 5 output cells (in that specific order):

[1] Total sum of hours during that shift =(B1-A1)*24
[2] Sum of normal day hours
[3] Sum of normal night hours
[4] Sum of additional day hours
[5] Sum of additional night hours

Night hours are from 22.00 to 06.00. If a person works more than 12 hours, then every hour after those 12 hours is counted as an "additional hour".

If it's during day time it's a "additional day hour", during night it's "additional night hour".

There is also another thing. Because of couple weird reasons, sometimes the time is formatted missing or adding one minute - so 15:59 needs to become 16:00, and 16:01 needs to be 16.00.

This formula needs to take that into the account.

So for example:

John Doe || 2016-07-25 16.00 || 2016-07-26 04.59

should give us: [1] 13 || [2] 6 || [3] 6 || [4] 0 || [5] 1

[jason.b75]

[QUOTE=newbienewb;4441668]There is also another thing. Because of couple weird reasons, sometimes the time is formatted missing or adding one minute - so 15:59 needs to become 16:00, and 16:01 needs to be 16.00./QUOTE]

Hello and Welcome newbie

Looking at your question, specifically the line that I've quoted, should the times always round to the nearest whole hour, or should it be to the nearest 30 min or 15 min interval?

What you're asking for makes perfect sense, but I don't think it will be an easy task. Most of it should be fairly simple, but the obvious curveball that I see is, for example, 19:00 to 08:00

3 normal day hours, 8 normal night hours, 1 more normal day hour, 1 additional day hour.

Because the normal day hours are split, it is going to make calculation difficult.

[newbienewb]

Thanks for your reply, @jason.b75.

Yes, the times should always round up to the nearest whole hour.

From what I gather and found around the internet by googling, it seems that it would need to be written in VBA.
Night times should be 'hardcoded' somehow into the 'brain' of this thing but yeah.. I'm clueless, don't know where to look next.

I'm not looking for someone to write it for me as I would like to learn how to do it but if someone could point me in the right direction that would be awesome.
However, I won't complain if someone does write it for me

[jason.b75]

From what I gather and found around the internet by googling, it seems that it would need to be written in VBA.

The internet can be a great source of unreliable information.

Step 1, create a named range using the following criteria. (to create a named range, go to the formulas tab on the excel ribbon, then click 'Name Manager' and 'New' and enter the criteria into the boxes with the same names).

Name: TestString
Refers to: =REPT(REPT("N",6)&REPT("D",16)&REPT("N",2),2)

Assuming start date and time in B2, finish date and time in C2, enter the following formulas into the relevant cells.

D2 =ROUND((C2-B2)*24,0)
E2 =SUMPRODUCT(--(MID(TestString,ROW(INDEX($A:$A,(ROUND(MOD(B2,1)*24,0)+1)):INDEX($A:$A,MIN(ROUND(MOD(B2,1)*24,0)+12,ROUND(MOD(C2,1)*24,0)+IF(INT(C2)>B2,24,0)))),1)="D"))
F2 =SUMPRODUCT(--(MID(TestString,ROW(INDEX($A:$A,(ROUND(MOD(B2,1)*24,0)+1)):INDEX($A:$A,MIN(ROUND(MOD(B2,1)*24,0)+12,ROUND(MOD(C2,1)*24,0)+IF(INT(C2)>B2,24,0)))),1)="N"))
G2 =IF(D2<=12,0,SUMPRODUCT(--(MID(TestString,ROW(INDEX($A:$A,(ROUND(MOD(B2,1)*24,0)+13)):INDEX($A:$A,ROUND(MOD(C2,1)*24,0)+IF(INT(C2)>B2,24,0))),1)="D")))
H2 =IF(D2<=12,0,SUMPRODUCT(--(MID(TestString,ROW(INDEX($A:$A,(ROUND(MOD(B2,1)*24,0)+13)):INDEX($A:$A,ROUND(MOD(C2,1)*24,0)+IF(INT(C2)>B2,24,0))),1)="N")))

These will return the 5 results in the same order as you listed them in post #1.

Have a play and see if you can figure out how they work (it's a good way to learn).

[newbienewb]

Thank you @jason.b75!

It works as described, but I've got one problem - it rounds up the hours to the nearest hour, even if it's half an hour.

I was not specific enough, sorry - I've said that it needs to round to nearest whole hour but in reality I need it to round up to half hour.

So, 15:59 becomes 16:00, 16:01 becomes 16:00 and 16:30 stays as 16:30.

After googling around I've found about CEILING, FLOOR, ROUND, ROUNDDOWN, ROUNDUP functions but it seems that they are fixed on rounding up or down which will result in messing up either 15:59 or 16:01...