# A Formula to calculate Special Hours

#### Arnolf

##### Board Regular
Hello
I need to calculate Special Hours between 2 dates.
Special hours start Saturdays at 15:00 and end Mondays at 7:00 .
Attached is my Sheet with the results required in Column F.
Arnolf
sphours.xls
ABCDEF
1SaturdayMonday
215:0007:00
3
4Date 1Start HourDate 2End HourTotal HoursSpecial Hours
515/09/200517:0017/09/200515:0046:0000:00
615/09/200521:0017/09/200516:0043:0001:00
716/09/200514:0017/09/200520:0030:0005:00
817/09/200511:0018/09/200519:0032:0028:00
917/09/200515:0019/09/200507:0040:0040:00
1017/09/200515:0019/09/200509:0042:0040:00
1118/09/200508:0019/09/200507:0023:0023:00
1219/09/200505:0019/09/200518:0013:0002:00
1323/09/200515:0004/10/200511:00260:0080:00
1405/10/200508:0027/10/200517:00537:00120:00
1510/10/200519:0022/10/200516:00285:0041:00
1612/10/200513:0027/12/200510:001821:00440:00
Sheet1

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You could do something to the effect of
IF(AND(WEEKDAY(B2,1)=7,D2>=15),D2-15,0)

Nope. It doesn't seem to work.
Thanks.

Adjust to match the format that your time is in. You might need to nest the times in TIMEVALUE function. But it will definatly work.

Ok. Perhaps I am missing something here. (I wish I was more creative )
I enclose the Sheet with your formula.
One question: Why the reference of cell D2 ?
sphours.xls
ABCDEFG
1SaturdayMonday
215:0007:00RESULTSYOUR
3REQUIREDFORMULA
4Date 1Start HourDate 2End HourTotal HoursSpecial Hours
515/09/200517:0017/09/200515:0046:0000:000
615/09/200521:0017/09/200516:0043:0001:000
716/09/200514:0017/09/200520:0030:0005:000
817/09/200511:0018/09/200519:0032:0028:000
917/09/200515:0019/09/200507:0040:0040:000
1017/09/200515:0019/09/200509:0042:0040:000
1118/09/200508:0019/09/200507:0023:0023:000
1219/09/200505:0019/09/200518:0013:0002:000
1323/09/200515:0004/10/200511:00260:0080:000
1405/10/200508:0027/10/200517:00537:00120:000
1510/10/200519:0022/10/200516:00285:0041:000
1612/10/200513:0027/12/200510:001821:00440:000
Sheet1

In this example the formula in g5 should be
=IF(AND(WEEKDAY(B\$5,1)=7,D\$5>=15),D\$5-15,0)
If that does not work do this:
Highlight Column D change the formatting to "General" and tell me what the value in D5 is.

Nope. I still don't get the results.
Enclose the Sheet with column D (formatting "General").
sphours.xls
ABCDEFG
1SaturdayMonday
215:0007:00RESULTSYOUR
3REQUIREDFORMULA
4Date 1Start HourDate 2End HourTotal HoursSpecial Hours
515/09/200517:0017/09/20050.62546:0000:000
615/09/200521:0017/09/20050.66666666743:0001:000
716/09/200514:0017/09/20050.83333333330:0005:000
817/09/200511:0018/09/20050.79166666732:0028:000
917/09/200515:0019/09/20050.29166666740:0040:000
1017/09/200515:0019/09/20050.37542:0040:000
1118/09/200508:0019/09/20050.29166666723:0023:000
1219/09/200505:0019/09/20050.7513:0002:000
1323/09/200515:0004/10/20050.458333333260:0080:000
1405/10/200508:0027/10/20050.708333333537:00120:000
1510/10/200519:0022/10/20050.666666667285:0041:000
1612/10/200513:0027/12/20050.4166666671821:00440:000
Sheet1

Try:
IF(AND(WEEKDAY(B2,1)=7,D2>=TIMEVALUE("15:00"),D2-TIMEVALUE("15:00"),0)

Now, I am seeing values. But, the formula shows some unexpected results (cells color pink).
sphours.xls
ABCDEFG
1SaturdayMonday
215:0007:00RESULTSYOUR
3REQUIREDFORMULA
4Date 1Start HourDate 2End HourTotal HoursSpecial Hours
515/09/200517:0017/09/200515:0046:0000:0000:00
615/09/200521:0017/09/200516:0043:0001:0001:00
716/09/200514:0017/09/200520:0030:0005:0005:00
817/09/200511:0018/09/200519:0032:0028:0004:00
917/09/200515:0019/09/200507:0040:0040:0000:00
1017/09/200515:0019/09/200509:0042:0040:0000:00
1118/09/200508:0019/09/200507:0023:0023:0000:00
1219/09/200505:0019/09/200518:0013:0002:0003:00
1323/09/200515:0004/10/200511:00260:0080:0000:00
1405/10/200508:0027/10/200517:00537:00120:0002:00
1510/10/200519:0022/10/200516:00285:0041:0001:00
1612/10/200513:0027/12/200510:001821:00440:0000:00
Sheet1

When I was going to troubleshoot this I notice something interesting.
The value in cell E5 shows 46. When I use your time formula I show 22. How do you have E5 formatted?

Replies
4
Views
504
Replies
1
Views
450

1,196,239
Messages
6,014,166
Members
441,807
Latest member
sjkenjalo

### 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.

### Which adblocker are you using?

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

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