Calculations of hours according to different dates and times

Omer_K

Board Regular
Joined
Apr 9, 2017
Messages
76
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
115
Office Version
  1. 2016
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,634
Members
410,696
Latest member
JTrehan
Top