Calculating Time

kkoz84 · Apr 27, 2018

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]

kkoz84 · Apr 27, 2018

Apologies, business hours for Saturday should be 06:00-12:00, not 06:00-16:00

thisoldman · Apr 27, 2018

As you didn't include your current almost-working formula, I'm unsure of exactly what correction the formula needs. Perhaps this will help; it counts the Sundays between a Start date, A5, and an End date, B5:

=INT((B5-A5-WEEKDAY(B5))/7)+1

kkoz84 · Apr 27, 2018

thisoldman said:
As you didn't include your current almost-working formula, I'm unsure of exactly what correction the formula needs. Perhaps this will help; it counts the Sundays between a Start date, A5, and an End date, B5:

=INT((B5-A5-WEEKDAY(B5))/7)+1

Thank you for advice. I am looking for full formula as my is just really force brute large formula with tonnes of iffs and fails...

thisoldman · Apr 27, 2018

I'm pretty sure this works.

Book1

A

B

C

D

E

F

G

1

Mon

Tue

Wed

Thu

Fri

Sat

Sun

2

06:00

OFF

3

16:00

12:00

OFF

4

5

Start Time

End Time

Hours

6

Fri 2018-04-20 07:00

Fri 2018-04-20 13:00

6

7

Fri 2018-04-20 07:00

Fri 2018-04-20 16:00

9

8

Fri 2018-04-20 07:00

Sat 2018-04-21 10:00

13

9

Sat 2018-04-21 10:00

Sat 2018-04-21 11:00

1

10

Sat 2018-04-21 11:00

Wed 2018-04-25 12:00

27

11

Sat 2018-04-21 11:00

Wed 2018-05-02 12:00

83

12

Sat 2018-04-21 11:00

Sat 2018-04-28 10:00

55

13

Sat 2018-04-21 11:00

Wed 2018-05-09 12:00

139

14

Sat 2018-04-21 11:00

Wed 2018-05-16 12:00

195

15

0

Sheet1

Cell Formulas
Range	Formula
C6	=IF(INT(A6)=INT(B6), 24(B6-A6), 24(INDEX($A$3:$F$3,WEEKDAY(A6,2))-A6+INT(A6) +B6-INT(B6)-6/24) +10MAX(NETWORKDAYS.INTL(A6+1,B6-1,11),0) -4(MAX(INT((B6-A6-WEEKDAY(B6))/6),0)))

IF(INT(A6)=INT(B6), -> If the start and end are on the same day, then
24*(B6-A6), -> subtract start from end and multiply by 24 to convert to hours, else ...

... Then it becomes a nightmare to do in one cell. Here are the parts.

Get the first day's datetimes:
lookup the the first day's end time, INDEX($A$3:$F$3,WEEKDAY(A15,2))
and subtract the first day's start time, -A15+INT(A15)

Get the last day's datetimes:
calculate the end time, B15-INT(B15)
and subtract the start time, -6/24

Add the first and last days datetimes, above, and multiply by 24 to convert to hours.

Add 10 hours for each full workday between start and end:
Get the number of full working days between the start time and the end time, not counting Sundays, and multiply by ten hours per full working day.
+10*MAX(NETWORKDAYS.INTL(A15+1,B15-1,11),0)

Correct the total hours for the full Saturdays worked:
Only six hours are possible on Saturday. Count the number of full Saturdays between the start and end dates. Multiply that value by four to get the hours not worked. Subtract that value from the previous total.
-4*(MAX(INT((B6-A6-WEEKDAY(B6))/6),0))

kkoz84 · Apr 28, 2018

thisoldman said:
I'm pretty sure this works.

A B C D E F G
OFF
OFF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D6DCE4"]Mon[/TD]
[TD="bgcolor: #D6DCE4"]Tue[/TD]
[TD="bgcolor: #D6DCE4"]Wed[/TD]
[TD="bgcolor: #D6DCE4"]Thu[/TD]
[TD="bgcolor: #D6DCE4"]Fri[/TD]
[TD="bgcolor: #D6DCE4"]Sat[/TD]
[TD="bgcolor: #D6DCE4"]Sun[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]12:00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #D6DCE4"]Start Time[/TD]
[TD="bgcolor: #D6DCE4"]End Time[/TD]
[TD="bgcolor: #D6DCE4"]Hours[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Fri 2018-04-20 07:00[/TD]
[TD="align: right"]Fri 2018-04-20 13:00[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Fri 2018-04-20 07:00[/TD]
[TD="align: right"]Fri 2018-04-20 16:00[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]Fri 2018-04-20 07:00[/TD]
[TD="align: right"]Sat 2018-04-21 10:00[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]Sat 2018-04-21 10:00[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]Wed 2018-04-25 12:00[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]Wed 2018-05-02 12:00[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]Sat 2018-04-28 10:00[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]Wed 2018-05-09 12:00[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]Wed 2018-05-16 12:00[/TD]
[TD="align: right"]195[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=IF(INT(A6)=INT(B6), 24*(B6-A6), 24*(INDEX($A$3:$F$3,WEEKDAY(A6,2))-A6+INT(A6)
+B6-INT(B6)-6/24)
+10*MAX(NETWORKDAYS.INTL(A6+1,B6-1,11),0)
-4*(MAX(INT((B6-A6-WEEKDAY(B6))/6),0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

IF(INT(A6)=INT(B6), -> If the start and end are on the same day, then
24*(B6-A6), -> subtract start from end and multiply by 24 to convert to hours, else ...

... Then it becomes a nightmare to do in one cell. Here are the parts.

Get the first day's datetimes:
lookup the the first day's end time, INDEX($A$3:$F$3,WEEKDAY(A15,2))
and subtract the first day's start time, -A15+INT(A15)

Get the last day's datetimes:
calculate the end time, B15-INT(B15)
and subtract the start time, -6/24

Add the first and last days datetimes, above, and multiply by 24 to convert to hours.

Add 10 hours for each full workday between start and end:
Get the number of full working days between the start time and the end time, not counting Sundays, and multiply by ten hours per full working day.
+10*MAX(NETWORKDAYS.INTL(A15+1,B15-1,11),0)

Correct the total hours for the full Saturdays worked:
Only six hours are possible on Saturday. Count the number of full Saturdays between the start and end dates. Multiply that value by four to get the hours not worked. Subtract that value from the previous total.
-4*(MAX(INT((B6-A6-WEEKDAY(B6))/6),0))

wow its amazing thank you so much!! I have one question though , how could I adjust formula if any other day will become "OFF" day or working hours will change. Fox example working hours would change for Monday only, or for Saturday only or for Mon-Friday. Is there any way to adjust formula with changing conditions?

Best regards

thisoldman · Apr 28, 2018

I moved the daily table. I had to extend it to two weeks—the calculations got too complicated for me with a one-week look up table. I've added the capability to correct the hours for up to twelve holidays.

There will be problems if you try to calculate hours through a period when there's a schedule change. As long as the schedule is the same for all the date-times in columns A and B everything works well. When there is a schedule change, make a new copy of the sheet and edit the schedule there.

All the cells containing formulas have a pale yellow fill. The cells for the holiday dates have been filled with pink.

Book1

A

B

C

D

E

F

G

H

1

Start Time

End Time

Hours

Holidays

Hrs

Hrs/wk

2

Fri 2018-04-20 07:00

Fri 2018-04-20 13:00

6

2018-01-01

10

56

3

Fri 2018-04-20 07:00

Fri 2018-04-20 16:00

9

2018-05-15

10

4

Fri 2018-04-20 07:00

Mon 2018-04-23 10:00

19

2018-05-28

10

5

Sat 2018-04-21 10:00

Sat 2018-04-21 11:00

1

2018-07-04

10

6

Thu 2018-04-19 11:00

Wed 2018-04-25 12:00

47

2018-09-03

10

7

Sat 2018-04-14 11:00

Fri 2018-04-20 12:00

47

2018-11-22

10

8

Sat 2018-04-21 11:00

Wed 2018-05-02 13:00

84

0

9

Mon 2018-04-23 11:00

Mon 2018-04-30 10:00

55

0

10

Sat 2018-04-21 11:00

Sat 2018-04-28 10:00

55

0

11

Sat 2018-04-21 11:00

Wed 2018-05-09 12:00

139

0

12

Sat 2018-04-21 11:00

Wed 2018-05-16 12:00

185

0

13

0

2018-12-25

10

Sheet1

Cell Formulas
Range	Formula
H2	=24*(SUMIF(J3:P3,"<>""OFF""")-SUMIF(J2:P2,"<>""OFF"""))
C2	=IF(INT(A2)=INT(B2),24(B2-A2),24(INDEX($J$3:$P$3,WEEKDAY(A2,2))-A2+INT(A2)+B2-INT(B2)-INDEX($J$2:$P$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))))))+INT((B2-A2)/7)*$H$2-SUMIFS($F$2:$F$13,$E$2:$E$13,">"&A2,$E$2:$E$13,"<"&B2))
F2	=NOT(ISBLANK(E2))24(INDEX($J$3:$P$3,,WEEKDAY(E2,2))-INDEX($J$2:$P$2,,WEEKDAY(E2,2)))

Book1

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

2

06:00

OFF

06:00

OFF

3

16:00

12:00

OFF

16:00

12:00

OFF

Sheet1

Cell Formulas
Range	Formula
Q2	=J2

Yes, it's a monster. I should have used helper columns, but once I started down the path, I was too stubborn to admit I made a wrong turn.

You can download the workbook from https://www.dropbox.com/s/6g8rpnws2ag4gea/calculating_time.xlsx?dl=0

kkoz84 · Apr 29, 2018

You have not seen my attempt to do this formula lol! That was 4 times longer and did not work!

I really appreciate your help. Not sure if I am doing something wrong but I get incorrect calculations if I copy new dates in, see below row 10,12, 13 showing incorrect results.

Would you know why would that be?

Best regards

[TABLE="width: 316"]
<tbody>[TR]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 06:55[/TD]
[TD]Mon 23-Apr-18 07:25[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 08:10[/TD]
[TD]Mon 23-Apr-18 08:14[/TD]
[TD="align: right"]0.067[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 09:41[/TD]
[TD]Mon 23-Apr-18 10:06[/TD]
[TD="align: right"]0.42[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 11:38[/TD]
[TD]Mon 23-Apr-18 14:08[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 14:53[/TD]
[TD]Mon 23-Apr-18 15:29[/TD]
[TD="align: right"]0.6[/TD]
[/TR]
[TR]
[TD]Tue 24-Apr-18 07:44[/TD]
[TD]Tue 24-Apr-18 08:02[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD]Tue 24-Apr-18 08:47[/TD]
[TD]Tue 24-Apr-18 10:32[/TD]
[TD="align: right"]1.75[/TD]
[/TR]
[TR]
[TD]Tue 24-Apr-18 11:54[/TD]
[TD]Tue 24-Apr-18 13:24[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD]Tue 24-Apr-18 14:54[/TD]
[TD]Wed 25-Apr-18 15:09[/TD]
[TD="align: right"]66.25[/TD]
[/TR]
[TR]
[TD]Thu 26-Apr-18 07:44[/TD]
[TD]Thu 26-Apr-18 11:44[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Thu 26-Apr-18 12:59[/TD]
[TD]Fri 27-Apr-18 07:31[/TD]
[TD="align: right"]60.54[/TD]
[/TR]
[TR]
[TD]Fri 27-Apr-18 08:46[/TD]
[TD]Sat 28-Apr-18 07:31[/TD]
[TD="align: right"]64.75[/TD]
[/TR]
[TR]
[TD]Sat 28-Apr-18 08:31[/TD]
[TD]Mon 30-Apr-18 08:56[/TD]
[TD="align: right"]6.412[/TD]
[/TR]
</tbody>[/TABLE]

kkoz84 · Apr 29, 2018

thisoldman said:
I moved the daily table. I had to extend it to two weeks—the calculations got too complicated for me with a one-week look up table. I've added the capability to correct the hours for up to twelve holidays.

There will be problems if you try to calculate hours through a period when there's a schedule change. As long as the schedule is the same for all the date-times in columns A and B everything works well. When there is a schedule change, make a new copy of the sheet and edit the schedule there.

All the cells containing formulas have a pale yellow fill. The cells for the holiday dates have been filled with pink.

A B C D E F G H

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #EBEBEB"]Start Time[/TD]
[TD="bgcolor: #EBEBEB"]End Time[/TD]
[TD="bgcolor: #EBEBEB"]Hours[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EBEBEB"]Holidays[/TD]
[TD="bgcolor: #EBEBEB"]Hrs[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EBEBEB"]Hrs/wk[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Fri 2018-04-20 07:00[/TD]
[TD="align: right"]Fri 2018-04-20 13:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"]2018-01-01[/TD]
[TD="bgcolor: #FFFBE6, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFBE6, align: right"]56[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Fri 2018-04-20 07:00[/TD]
[TD="align: right"]Fri 2018-04-20 16:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"]2018-05-15[/TD]
[TD="bgcolor: #FFFBE6, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Fri 2018-04-20 07:00[/TD]
[TD="align: right"]Mon 2018-04-23 10:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"]2018-05-28[/TD]
[TD="bgcolor: #FFFBE6, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]Sat 2018-04-21 10:00[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"]2018-07-04[/TD]
[TD="bgcolor: #FFFBE6, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Thu 2018-04-19 11:00[/TD]
[TD="align: right"]Wed 2018-04-25 12:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]47[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"]2018-09-03[/TD]
[TD="bgcolor: #FFFBE6, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Sat 2018-04-14 11:00[/TD]
[TD="align: right"]Fri 2018-04-20 12:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]47[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"]2018-11-22[/TD]
[TD="bgcolor: #FFFBE6, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]Wed 2018-05-02 13:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]84[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"][/TD]
[TD="bgcolor: #FFFBE6, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]Mon 2018-04-23 11:00[/TD]
[TD="align: right"]Mon 2018-04-30 10:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"][/TD]
[TD="bgcolor: #FFFBE6, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]Sat 2018-04-28 10:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"][/TD]
[TD="bgcolor: #FFFBE6, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]Wed 2018-05-09 12:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]139[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"][/TD]
[TD="bgcolor: #FFFBE6, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]Sat 2018-04-21 11:00[/TD]
[TD="align: right"]Wed 2018-05-16 12:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]185[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"][/TD]
[TD="bgcolor: #FFFBE6, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFBE6, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE1E0, align: right"]2018-12-25[/TD]
[TD="bgcolor: #FFFBE6, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=24*(SUMIF(J3:P3,"<>""OFF""")-SUMIF(J2:P2,"<>""OFF"""))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(INT(A2)=INT(B2),24*(B2-A2),24*(INDEX($J$3:$P$3,WEEKDAY(A2,2))-A2+INT(A2)+B2-INT(B2)-INDEX($J$2:$P$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))))))+INT((B2-A2)/7)*$H$2-SUMIFS($F$2:$F$13,$E$2:$E$13,">"&A2,$E$2:$E$13,"<"&B2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=NOT(ISBLANK(E2))*24*(INDEX($J$3:$P$3,,WEEKDAY(E2,2))-INDEX($J$2:$P$2,,WEEKDAY(E2,2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

J K L M N O P Q R S T U V W
OFF
OFF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #EBEBEB"]Mon[/TD]
[TD="bgcolor: #EBEBEB"]Tue[/TD]
[TD="bgcolor: #EBEBEB"]Wed[/TD]
[TD="bgcolor: #EBEBEB"]Thu[/TD]
[TD="bgcolor: #EBEBEB"]Fri[/TD]
[TD="bgcolor: #EBEBEB"]Sat[/TD]
[TD="bgcolor: #EBEBEB"]Sun[/TD]
[TD="bgcolor: #EBEBEB"]Mon[/TD]
[TD="bgcolor: #EBEBEB"]Tue[/TD]
[TD="bgcolor: #EBEBEB"]Wed[/TD]
[TD="bgcolor: #EBEBEB"]Thu[/TD]
[TD="bgcolor: #EBEBEB"]Fri[/TD]
[TD="bgcolor: #EBEBEB"]Sat[/TD]
[TD="bgcolor: #EBEBEB"]Sun[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]06:00[/TD]

[TD="bgcolor: #FFFBE6, align: right"]06:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]06:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]06:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]06:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]06:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]06:00[/TD]
[TD="bgcolor: #FFFBE6"]OFF[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]12:00[/TD]

[TD="bgcolor: #FFFBE6, align: right"]16:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]16:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]16:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]16:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]16:00[/TD]
[TD="bgcolor: #FFFBE6, align: right"]12:00[/TD]
[TD="bgcolor: #FFFBE6"]OFF[/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Q2[/TH]
[TD="align: left"]=J2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes, it's a monster. I should have used helper columns, but once I started down the path, I was too stubborn to admit I made a wrong turn.

You can download the workbook from https://www.dropbox.com/s/6g8rpnws2ag4gea/calculating_time.xlsx?dl=0

You have not seen my attempt to do this formula lol! That was 4 times longer and did not work!

I really appreciate your help. Not sure if I am doing something wrong but I get incorrect calculations if I copy new dates in, see below row 10,12, 13 showing incorrect results.

Would you know why would that be?

Best regards

[TABLE="class: cms_table, width: 316"]
<tbody>[TR]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 06:55[/TD]
[TD]Mon 23-Apr-18 07:25[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 08:10[/TD]
[TD]Mon 23-Apr-18 08:14[/TD]
[TD="align: right"]0.067[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 09:41[/TD]
[TD]Mon 23-Apr-18 10:06[/TD]
[TD="align: right"]0.42[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 11:38[/TD]
[TD]Mon 23-Apr-18 14:08[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD]Mon 23-Apr-18 14:53[/TD]
[TD]Mon 23-Apr-18 15:29[/TD]
[TD="align: right"]0.6[/TD]
[/TR]
[TR]
[TD]Tue 24-Apr-18 07:44[/TD]
[TD]Tue 24-Apr-18 08:02[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD]Tue 24-Apr-18 08:47[/TD]
[TD]Tue 24-Apr-18 10:32[/TD]
[TD="align: right"]1.75[/TD]
[/TR]
[TR]
[TD]Tue 24-Apr-18 11:54[/TD]
[TD]Tue 24-Apr-18 13:24[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD]Tue 24-Apr-18 14:54[/TD]
[TD]Wed 25-Apr-18 15:09[/TD]
[TD="align: right"]66.25[/TD]
[/TR]
[TR]
[TD]Thu 26-Apr-18 07:44[/TD]
[TD]Thu 26-Apr-18 11:44[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Thu 26-Apr-18 12:59[/TD]
[TD]Fri 27-Apr-18 07:31[/TD]
[TD="align: right"]60.54[/TD]
[/TR]
[TR]
[TD]Fri 27-Apr-18 08:46[/TD]
[TD]Sat 28-Apr-18 07:31[/TD]
[TD="align: right"]64.75[/TD]
[/TR]
[TR]
[TD]Sat 28-Apr-18 08:31[/TD]
[TD]Mon 30-Apr-18 08:56[/TD]
[TD="align: right"]6.412[/TD]
[/TR]
</tbody>[/TABLE]

markmzz · Apr 29, 2018

Hi!

Try this in C2 and copy down:

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

=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

Calculating Time

kkoz84

Board Regular

Excel Facts

kkoz84

Board Regular

thisoldman

Well-known Member

kkoz84

Board Regular

thisoldman

Well-known Member

kkoz84

Board Regular

thisoldman

Well-known Member

kkoz84

Board Regular

kkoz84

Board Regular

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

Calculating Time

Board Regular

Excel Facts

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock