Calculations of hours according to different dates and times

[Omer_K]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Cost From Sunday Until Thursday						Cost For Friday And The eve of a Holiday						Cost For Saturday And Holiday						the eve of a holiday		Holiday
2	From Months	Begin Hour	End Hour	Costs For Hour	Months		From Months	Begin Hour	End Hour	Costs	Months		From Months	Begin Hour	End Hour	Costs For Hour	Months		17/06/2020		18/06/2020
3	1	00:00	06:00	28.3	1 to 2		1	00:00	16:00	28.3	1 to 2		1	00:00	17:00	28.3	1 to 2		20/02/2020		21/02/2020
4	1	06:00	08:00	48.89			1	16:00	20:00	48.89			1	17:00	19:00	82.66			05/04/2020		06/04/2020
5	1	08:00	16:00	28.3			1	20:00	00:00	28.3			1	19:00	21:00	48.89			16/08/2020		17/08/2020
6	1	16:00	22:00	82.66			3	00:00	06:00	25	3 to 6		1	21:00	00:00	28.3			29/04/2020		30/04/2020
7	1	22:00	00:00	28.3			3	06:00	20:00	30.83			3	00:00	17:00	25	3 to 6		02/09/2020		03/09/2020
8	3	00:00	06:00	25	3 to 6		3	20:00	00:00	25			3	17:00	21:00	30.83			09/03/2020		10/03/2020
9	3	06:00	20:00	38.11			7	00:00	00:00	25.52	7 to 8		3	21:00	00:00	25
10	3	20:00	22:00	30.83			9	00:00	06:00	25	9 to 11		7	00:00	00:00	25.52	7 to 8
11	3	22:00	00:00	25			9	06:00	20:00	30.83			9	00:00	17:00	25	9 to 11
12	7	00:00	07:00	25.52	7 to 8		9	20:00	00:00	25			9	17:00	21:00	30.83
13	7	07:00	10:00	38.46			12	00:00	16:00	28.3	12		9	21:00	00:00	25
14	7	10:00	17:00	90.05			12	16:00	20:00	48.89			12	00:00	17:00	28.3	12
15	7	17:00	21:00	38.46			12	20:00	00:00	28.3			12	17:00	19:00	82.66
16	7	21:00	00:00	25.52									12	19:00	21:00	48.89
17	9	00:00	06:00	25	9 to 11								12	21:00	00:00	28.3
18	9	06:00	20:00	38.11
19	9	20:00	22:00	30.83
20	9	22:00	00:00	25
21	12	00:00	06:00	28.3	12
22	12	06:00	08:00	48.89
23	12	08:00	16:00	28.3
24	12	16:00	22:00	82.66
25	12	22:00	00:00	28.3
26											start_date	Start_time	End_date	End_time	Costs
27											16/06/2020	17:26:49	16/06/2020	18:24:48
28											20/06/2020	12:24:05	20/06/2020	13:19:21
29											16/01/2020	14:49:09	16/01/2020	15:07:20
30											07/06/2020	15:07:37	07/06/2020	15:14:30
31											04/05/2020	15:09:10	04/05/2020	15:32:44
32											26/02/2020	17:13:33	26/02/2020	17:36:09
33											17/04/2020	11:43:52	17/04/2020	17:43:31
34											13/03/2020	17:45:07	13/03/2020	17:48:37
35											12/01/2020	13:54:57	12/01/2020	14:20:13
36											30/04/2020	14:25:13	30/04/2020	14:32:43
37											06/07/2020	09:48:13	06/07/2020	11:17:44
38											30/03/2020	20:01:34	30/03/2020	21:28:41
39											21/02/2020	19:00:38	21/02/2020	19:21:05
Data

Cell Formulas
Range	Formula
U2:U8	U2	=S2+1

 

[Omer_K]

Any Ideas?

 

[Omer_K]

No one knows how to solve this ?

 

[Sulprobil]

A similar challenge has been discussed here:

Unsocial Hours Time Sheet Calculations

I am trying to build a sheet to calculate unsocial hours pay from a timesheet. The criteria for the unsocial hours is as follows: Monday - Friday 0700-2000 - No enhancement Monday - Friday 1900-0700 - 44% Saturday - 0700-0700 Sunday 44% Sunday - 0700-0700 Monday 88% example - Mon - Fri...

www.mrexcel.com

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.