Calculating Time

K

kkoz84

Board Regular
Joined
Sep 8, 2015
Messages
82
Hi,

i am having trouble to calculate time between two dates. I managed to get my formulas right to calculate time when date range is same day or two days, but i cannot find robust formula to skip "OFF" days or if date range is more than two days.

Please see below data.I would like to calculate results in column "Hours" based on the time range specified above for each day. If day is "OFF" then I would like to skip this day.

Results in "Hours" column explained as:

First result is 13 hours, because machine was running from 7:00 Am on Friday, so 9 hours plus 4 hours Saturday, so total of 13 hours.
Second result is 2 hours
Third result is 27 hours:1 hour Saturday + Sunday is skipped as its "OFF" + full Monday is 10 hours (06:00- 16:00)+full Tuesday is 10 hours (06:00- 16:00)+ 6 hours Wed (06:00-12:00), so total is 27 hours.

I hope I explained this well, I would greatly appreciate help.

Best regards

[TABLE="width: 584"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Mon[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Tue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Wed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Thu[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Fri[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Sat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Sun[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]06:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]06:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]06:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]06:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]06:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]06:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]OFF[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]16:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]16:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]16:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]16:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]16:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]16:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]OFF[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Start Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]End Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Hours[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Fri 20-Apr-18 07:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Sat 21-Apr-18 10:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Sat 21-Apr-18 10:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Sat 21-Apr-18 12:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Sat 21-Apr-18 11:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Wed 25-Apr-18 12:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]27[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Hi!

I fogot the times (Start Time and End Time):

[TABLE="class: grid, width: 896"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Hours[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Dom[/TD]
[TD="align: center"]Seg[/TD]
[TD="align: center"]Ter[/TD]
[TD="align: center"]Qua[/TD]
[TD="align: center"]Qui[/TD]
[TD="align: center"]Sex[/TD]
[TD="align: center"]Sáb[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]sex, 20/04/2018 07:00[/TD]
[TD="align: center"]sex, 20/04/2018 13:00[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]OFF[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]sex, 20/04/2018 07:00[/TD]
[TD="align: center"]sex, 20/04/2018 16:00[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]OFF[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]12:00:00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]sex, 20/04/2018 07:00[/TD]
[TD="align: center"]sáb, 21/04/2018 10:00[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]00:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]sáb, 21/04/2018 10:00[/TD]
[TD="align: center"]sáb, 21/04/2018 11:00[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]sáb, 21/04/2018 11:00[/TD]
[TD="align: center"]qua, 25/04/2018 12:00[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]sáb, 21/04/2018 11:00[/TD]
[TD="align: center"]qua, 02/05/2018 12:00[/TD]
[TD="align: center"]83[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]sáb, 21/04/2018 11:00[/TD]
[TD="align: center"]sáb, 28/04/2018 10:00[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Dom[/TD]
[TD="align: center"]Seg[/TD]
[TD="align: center"]Ter[/TD]
[TD="align: center"]Qua[/TD]
[TD="align: center"]Qui[/TD]
[TD="align: center"]Sex[/TD]
[TD="align: center"]Sáb[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]sáb, 21/04/2018 11:00[/TD]
[TD="align: center"]qua, 09/05/2018 12:00[/TD]
[TD="align: center"]139[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]sáb, 21/04/2018 11:00[/TD]
[TD="align: center"]qua, 16/05/2018 12:00[/TD]
[TD="align: center"]195[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]********************[/TD]
[TD="align: center"]********************[/TD]
[TD="align: center"]*****[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]*****[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz
 
Last edited:
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
markmzz said:
Hi!

Try this in C2 and copy down:

Array Formula - use Ctrl+Shift+Enter to enter the formula

Perfect it works thank you for solutions just did some testing !

=24*IF(INT(B2)-INT(A2)>0,
MAX(IFERROR(1*INDEX($F$2:$L$3,2,WEEKDAY(A2))-MEDIAN(MOD(A2,1),1*INDEX($F$2:$L$3,,WEEKDAY(A2))),0),0)+
MAX(IFERROR(MEDIAN(MOD(B2,1),1*INDEX($F$2:$L$3,,WEEKDAY(B2)))-1*INDEX($F$2:$L$3,1,WEEKDAY(B2)),0),0)+
(INT(B2)-INT(A2)>1)*SUM(LOOKUP(WEEKDAY(ROW(INDIRECT(INT(A2)+1&":"&INT(B2)-1))),{1;2;3;4;5;6;7},$F$4:$L$4)),
IFERROR(MEDIAN(MOD(B2,1),1*INDEX($F$2:$L$3,,WEEKDAY(B2)))-MEDIAN(MOD(A2,1),1*INDEX($F$2:$L$3,,WEEKDAY(A2))),0))


[TABLE="class: grid, width: 836"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Hours[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Dom[/TD]
[TD="align: center"]Seg[/TD]
[TD="align: center"]Ter[/TD]
[TD="align: center"]Qua[/TD]
[TD="align: center"]Qui[/TD]
[TD="align: center"]Sex[/TD]
[TD="align: center"]Sáb[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]sex, 20/04/2018[/TD]
[TD="align: center"]sex, 20/04/2018[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]OFF[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]sex, 20/04/2018[/TD]
[TD="align: center"]sex, 20/04/2018[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]OFF[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]16:00:00[/TD]
[TD="align: center"]12:00:00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]sex, 20/04/2018[/TD]
[TD="align: center"]sáb, 21/04/2018[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]00:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]10:00:00[/TD]
[TD="align: center"]06:00:00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]sáb, 21/04/2018[/TD]
[TD="align: center"]sáb, 21/04/2018[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]sáb, 21/04/2018[/TD]
[TD="align: center"]qua, 25/04/2018[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]sáb, 21/04/2018[/TD]
[TD="align: center"]qua, 02/05/2018[/TD]
[TD="align: center"]83[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]sáb, 21/04/2018[/TD]
[TD="align: center"]sáb, 28/04/2018[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]sáb, 21/04/2018[/TD]
[TD="align: center"]qua, 09/05/2018[/TD]
[TD="align: center"]139[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]sáb, 21/04/2018[/TD]
[TD="align: center"]qua, 16/05/2018[/TD]
[TD="align: center"]195[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]***************[/TD]
[TD="align: center"]***************[/TD]
[TD="align: center"]*****[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
Click to expand...



Perfect it works thank you for solutions just did some testing !
 
Upvote 0
I'm glad someone else tackled the problem and approached it in a different way. I did finally get my approach to work.

In C2 of my worksheet:
Code: 
=(INT(A2)=INT(B2))*24*(B2-A2)
+INT((B2-A2)/7)*$H$2
-SUMIFS($F$2:$F$13,$E$2:$E$13,">"&A2,$E$2:$E$13,"<"&B2)
+24*(INT(A2)<>INT(B2))
*(INDEX($J$3:$P$3,,WEEKDAY(A2,2))-(A2-INT(A2))
+B2-INT(B2)-INDEX($J$2:$P$2,,WEEKDAY(B2,2))
+((WEEKDAY(A2+1,2)=WEEKDAY(B2-1,2))*(WEEKDAY(A2+1,2)<>WEEKDAY(B2,2)))
  *(INDEX($J$3:$P$3,,WEEKDAY(A2+1,2))<>"OFF")*(SUM(INDEX($J$2:$P$3,,WEEKDAY(A2+1,2)))-0.5)
+((WEEKDAY(A2+1,2)<>WEEKDAY(B2-1,2))*(WEEKDAY(A2+1,2)<>WEEKDAY(B2,2)))
  *(SUM(INDEX($J$3:$P$3,,WEEKDAY(A2+1,2)):INDEX($J$3:$W$3,,WEEKDAY(B2-1,2)+7*(WEEKDAY(A2+1,2)>WEEKDAY(B2-1,2))))
   -SUM(INDEX($J$2:$P$2,,WEEKDAY(A2+1,2)):INDEX($J$2:$W$2,,WEEKDAY(B2-1,2)+7*(WEEKDAY(A2+1,2)>WEEKDAY(B2-1,2))))))
I updated the dropbox file.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
T
  • Question Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
890
Alex Blakenburg
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
M
  • Locked
  • Question Question
Trying to input time calculation so it does not change to 24 hour format
Replies
3
Views
176
Peter_SSs
Peter_SSs
J
  • Solved
Sum up columns based on values (date and time) in more than one column
Replies
3
Views
309
myall_blues
myall_blues

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

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