Calculations of hours according to different dates and times

Omer_K

Board Regular
Joined
Apr 9, 2017
Messages
103
Office Version
  1. 365
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.

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
189
Office Version
  1. 2021
Platform
  1. Windows
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.
 
Master Excel Bundle

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.

Forum statistics

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

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
Top