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

Mon

<tbody>
</tbody>
Tue

<tbody>
</tbody>
Wed

<tbody>
</tbody>
Thu

<tbody>
</tbody>
Fri

<tbody>
</tbody>
Sat

<tbody>
</tbody>
Sun

<tbody>
</tbody>
06:00

<tbody>
</tbody>
06:00

<tbody>
</tbody>
06:00

<tbody>
</tbody>
06:00

<tbody>
</tbody>
06:00

<tbody>
</tbody>
06:00

<tbody>
</tbody>
OFF

<tbody>
</tbody>
16:00

<tbody>
</tbody>
16:00

<tbody>
</tbody>
16:00

<tbody>
</tbody>
16:00

<tbody>
</tbody>
16:00

<tbody>
</tbody>
16:00

<tbody>
</tbody>
OFF

<tbody>
</tbody>
Start Time

<tbody>
</tbody>
End Time

<tbody>
</tbody>
Hours

<tbody>
</tbody>
Fri 20-Apr-18 07:00

<tbody>
</tbody>
Sat 21-Apr-18 10:00

<tbody>
</tbody>
13

<tbody>
</tbody>
Sat 21-Apr-18 10:00

<tbody>
</tbody>
Sat 21-Apr-18 12:00

<tbody>
</tbody>
2

<tbody>
</tbody>
Sat 21-Apr-18 11:00

<tbody>
</tbody>
Wed 25-Apr-18 12:00

<tbody>
</tbody>
27

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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
 
Upvote 0
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
Click to expand...

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...
 
Upvote 0
I'm pretty sure this works.


Book1
ABCDEFG
1MonTueWedThuFriSatSun
206:0006:0006:0006:0006:0006:00OFF
316:0016:0016:0016:0016:0012:00OFF
4
5Start TimeEnd TimeHours
6Fri 2018-04-20 07:00Fri 2018-04-20 13:006
7Fri 2018-04-20 07:00Fri 2018-04-20 16:009
8Fri 2018-04-20 07:00Sat 2018-04-21 10:0013
9Sat 2018-04-21 10:00Sat 2018-04-21 11:001
10Sat 2018-04-21 11:00Wed 2018-04-25 12:0027
11Sat 2018-04-21 11:00Wed 2018-05-02 12:0083
12Sat 2018-04-21 11:00Sat 2018-04-28 10:0055
13Sat 2018-04-21 11:00Wed 2018-05-09 12:00139
14Sat 2018-04-21 11:00Wed 2018-05-16 12:00195
150
Sheet1
Cell Formulas
RangeFormula
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) +10*MAX(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))
 
Upvote 0
thisoldman said:
I'm pretty sure this works.

ABCDEFG
1MonTueWedThuFriSatSun
206:0006:0006:0006:0006:0006:00OFF
316:0016:0016:0016:0016:0012:00OFF
4
5Start TimeEnd TimeHours
6Fri 2018-04-20 07:00Fri 2018-04-20 13:006
7Fri 2018-04-20 07:00Fri 2018-04-20 16:009
8Fri 2018-04-20 07:00Sat 2018-04-21 10:0013
9Sat 2018-04-21 10:00Sat 2018-04-21 11:001
10Sat 2018-04-21 11:00Wed 2018-04-25 12:0027
11Sat 2018-04-21 11:00Wed 2018-05-02 12:0083
12Sat 2018-04-21 11:00Sat 2018-04-28 10:0055
13Sat 2018-04-21 11:00Wed 2018-05-09 12:00139
14Sat 2018-04-21 11:00Wed 2018-05-16 12:00195
150

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
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)
+10*MAX(NETWORKDAYS.INTL(A6+1,B6-1,11),0)
-4*(MAX(INT((B6-A6-WEEKDAY(B6))/6),0)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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))
Click to expand...

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
 
Upvote 0
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
ABCDEFGH
1Start TimeEnd TimeHoursHolidaysHrsHrs/wk
2Fri 2018-04-20 07:00Fri 2018-04-20 13:0062018-01-011056
3Fri 2018-04-20 07:00Fri 2018-04-20 16:0092018-05-1510
4Fri 2018-04-20 07:00Mon 2018-04-23 10:00192018-05-2810
5Sat 2018-04-21 10:00Sat 2018-04-21 11:0012018-07-0410
6Thu 2018-04-19 11:00Wed 2018-04-25 12:00472018-09-0310
7Sat 2018-04-14 11:00Fri 2018-04-20 12:00472018-11-2210
8Sat 2018-04-21 11:00Wed 2018-05-02 13:00840
9Mon 2018-04-23 11:00Mon 2018-04-30 10:00550
10Sat 2018-04-21 11:00Sat 2018-04-28 10:00550
11Sat 2018-04-21 11:00Wed 2018-05-09 12:001390
12Sat 2018-04-21 11:00Wed 2018-05-16 12:001850
1302018-12-2510
Sheet1
Cell Formulas
RangeFormula
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
JKLMNOPQRSTUVW
1MonTueWedThuFriSatSunMonTueWedThuFriSatSun
206:0006:0006:0006:0006:0006:00OFF06:0006:0006:0006:0006:0006:00OFF
316:0016:0016:0016:0016:0012:00OFF16:0016:0016:0016:0016:0012:00OFF
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 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

Start TimeEnd TimeHours
Mon 23-Apr-18 06:55Mon 23-Apr-18 07:250.5
Mon 23-Apr-18 08:10Mon 23-Apr-18 08:140.067
Mon 23-Apr-18 09:41Mon 23-Apr-18 10:060.42
Mon 23-Apr-18 11:38Mon 23-Apr-18 14:082.5
Mon 23-Apr-18 14:53Mon 23-Apr-18 15:290.6
Tue 24-Apr-18 07:44Tue 24-Apr-18 08:020.3
Tue 24-Apr-18 08:47Tue 24-Apr-18 10:321.75
Tue 24-Apr-18 11:54Tue 24-Apr-18 13:241.5
Tue 24-Apr-18 14:54Wed 25-Apr-18 15:0966.25
Thu 26-Apr-18 07:44Thu 26-Apr-18 11:444
Thu 26-Apr-18 12:59Fri 27-Apr-18 07:3160.54
Fri 27-Apr-18 08:46Sat 28-Apr-18 07:3164.75
Sat 28-Apr-18 08:31Mon 30-Apr-18 08:566.412

<tbody>
</tbody>
 
Last edited:
Upvote 0
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.

ABCDEFGH
1Start TimeEnd TimeHoursHolidaysHrsHrs/wk
2Fri 2018-04-20 07:00Fri 2018-04-20 13:0062018-01-011056
3Fri 2018-04-20 07:00Fri 2018-04-20 16:0092018-05-1510
4Fri 2018-04-20 07:00Mon 2018-04-23 10:00192018-05-2810
5Sat 2018-04-21 10:00Sat 2018-04-21 11:0012018-07-0410
6Thu 2018-04-19 11:00Wed 2018-04-25 12:00472018-09-0310
7Sat 2018-04-14 11:00Fri 2018-04-20 12:00472018-11-2210
8Sat 2018-04-21 11:00Wed 2018-05-02 13:00840
9Mon 2018-04-23 11:00Mon 2018-04-30 10:00550
10Sat 2018-04-21 11:00Sat 2018-04-28 10:00550
11Sat 2018-04-21 11:00Wed 2018-05-09 12:001390
12Sat 2018-04-21 11:00Wed 2018-05-16 12:001850
1302018-12-2510

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
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)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



JKLMNOPQRSTUVW
1MonTueWedThuFriSatSunMonTueWedThuFriSatSun
206:0006:0006:0006:0006:0006:00OFF06:0006:0006:0006:0006:0006:00OFF
316:0016:0016:0016:0016:0012:00OFF16:0016:0016:0016:0016:0012:00OFF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
Q2=J2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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
Click to expand...


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

Start TimeEnd TimeHours
Mon 23-Apr-18 06:55Mon 23-Apr-18 07:250.5
Mon 23-Apr-18 08:10Mon 23-Apr-18 08:140.067
Mon 23-Apr-18 09:41Mon 23-Apr-18 10:060.42
Mon 23-Apr-18 11:38Mon 23-Apr-18 14:082.5
Mon 23-Apr-18 14:53Mon 23-Apr-18 15:290.6
Tue 24-Apr-18 07:44Tue 24-Apr-18 08:020.3
Tue 24-Apr-18 08:47Tue 24-Apr-18 10:321.75
Tue 24-Apr-18 11:54Tue 24-Apr-18 13:241.5
Tue 24-Apr-18 14:54Wed 25-Apr-18 15:0966.25
Thu 26-Apr-18 07:44Thu 26-Apr-18 11:444
Thu 26-Apr-18 12:59Fri 27-Apr-18 07:3160.54
Fri 27-Apr-18 08:46Sat 28-Apr-18 07:3164.75
Sat 28-Apr-18 08:31Mon 30-Apr-18 08:566.412

<tbody>
</tbody>
 
Upvote 0
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))


ABCDEFGHIJKLM
1Start TimeEnd TimeHoursDomSegTerQuaQuiSexSáb
2sex, 20/04/2018sex, 20/04/20186StartOFF06:00:0006:00:0006:00:0006:00:0006:00:0006:00:00
3sex, 20/04/2018sex, 20/04/20189EndOFF16:00:0016:00:0016:00:0016:00:0016:00:0012:00:00
4sex, 20/04/2018sáb, 21/04/201813Total00:00:0010:00:0010:00:0010:00:0010:00:0010:00:0006:00:00
5sáb, 21/04/2018sáb, 21/04/20181
6sáb, 21/04/2018qua, 25/04/201827
7sáb, 21/04/2018qua, 02/05/201883
8sáb, 21/04/2018sáb, 28/04/201855
9sáb, 21/04/2018qua, 09/05/2018139
10sáb, 21/04/2018qua, 16/05/2018195
110
*********************************************************************************************************

<tbody>
</tbody>


Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Shift bands
2 3
Replies
29
Views
1K
felixstraube
F
AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
312
AOB
AOB
A
  • Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
196
Aadya
A
alm395
  • Question
Coverage By Hour
Replies
3
Views
251
alm395
alm395
B
  • Solved
Sumifs Hours Worked Between Two Dates and Times with Multiple Criteria
2 3
Replies
24
Views
768
Fluff
Fluff

Forum statistics

Threads
1,215,831
Messages
6,127,148
Members
449,364
Latest member
AlienSx

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