# Calculations of hours according to different dates and times

#### Omer_K

##### Board Regular
Hello everyone,
In the table I have attached there is a pricing of cost per hour according to months, Fridays, Saturdays, holiday eves and holidays (holiday eves and holidays appear on the dates on the side)
I would like to know how to perform the calculation in the range of cells O27: O40 according to the costs in the table.
The main problem is what happens when there is time that it is at 2 different pricing,

Thank you,

Data.xlsx
ABCDEFGHIJKLMNOPQRSTU
1Cost From Sunday Until ThursdayCost For Friday And The eve of a HolidayCost For Saturday And Holidaythe eve of a holidayHoliday
2From MonthsBegin HourEnd HourCosts For HourMonthsFrom MonthsBegin HourEnd HourCostsMonthsFrom MonthsBegin HourEnd HourCosts For HourMonths17/06/202018/06/2020
3100:0006:0028.31 to 2100:0016:0028.31 to 2100:0017:0028.31 to 220/02/202021/02/2020
4106:0008:0048.89116:0020:0048.89117:0019:0082.6605/04/202006/04/2020
5108:0016:0028.3120:0000:0028.3119:0021:0048.8916/08/202017/08/2020
6116:0022:0082.66300:0006:00253 to 6121:0000:0028.329/04/202030/04/2020
7122:0000:0028.3306:0020:0030.83300:0017:00253 to 602/09/202003/09/2020
8300:0006:00253 to 6320:0000:0025317:0021:0030.8309/03/202010/03/2020
9306:0020:0038.11700:0000:0025.527 to 8321:0000:0025
10320:0022:0030.83900:0006:00259 to 11700:0000:0025.527 to 8
11322:0000:0025906:0020:0030.83900:0017:00259 to 11
12700:0007:0025.527 to 8920:0000:0025917:0021:0030.83
13707:0010:0038.461200:0016:0028.312921:0000:0025
14710:0017:0090.051216:0020:0048.891200:0017:0028.312
15717:0021:0038.461220:0000:0028.31217:0019:0082.66
16721:0000:0025.521219:0021:0048.89
17900:0006:00259 to 111221:0000:0028.3
18906:0020:0038.11
19920:0022:0030.83
20922:0000:0025
211200:0006:0028.312
221206:0008:0048.89
231208:0016:0028.3
241216:0022:0082.66
251222:0000:0028.3
26start_dateStart_timeEnd_dateEnd_timeCosts
2716/06/202017:26:4916/06/202018:24:48
2820/06/202012:24:0520/06/202013:19:21
2916/01/202014:49:0916/01/202015:07:20
3007/06/202015:07:3707/06/202015:14:30
3104/05/202015:09:1004/05/202015:32:44
3226/02/202017:13:3326/02/202017:36:09
3317/04/202011:43:5217/04/202017:43:31
3413/03/202017:45:0713/03/202017:48:37
3512/01/202013:54:5712/01/202014:20:13
3630/04/202014:25:1330/04/202014:32:43
3706/07/202009:48:1306/07/202011:17:44
3830/03/202020:01:3430/03/202021:28:41
3921/02/202019:00:3821/02/202019:21:05
Data
Cell Formulas
RangeFormula
U2:U8U2=S2+1

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Any Ideas?

#### Omer_K

##### Board Regular
No one knows how to solve this ?

#### Sulprobil

##### Board Regular
A similar challenge has been discussed here:

If you use my second link, I suggest you call this function with three different time tables and you make sure that the time slots do not overlap.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,164,601
Messages
5,838,287
Members
430,537
Latest member
Antonio11

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

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