Formula help

[Jaspal80]

I have below table and the screen print showing what I need.

I am trying to populate 'Desired output' column with data from Values column.

when the dates are weekend then the next working day should be working day * sunday * saturday

the problem is when we have holiday which can fall on any day of the month.

so if holiday is on Friday then the next working day should be values from Monday * sun * sat * friday

and if holiday is on a Monday then the next working day should have values from Tuesday * mon * sun * sat

and if its  in between weekday like wed then thus should have thus * wed

and this has to work if we have multiple holidays according - so if we have Friday and Monday as holiday - then on tues = Tues * mon * sun* sat * fri

i was trying to use multirow but its getting bit completed and need help.

you can assume any day as holiday - I have taken 19th as holiday in the example shared.

Thank you for your help.

Values	DateTime_Out	weekday 6 Sat and 0 Sun	Desired output	Comments	Comments
0.10	9/1/2022	4	0.10
0.20	9/2/2022	5	0.20
0.30	9/3/2022	6	0.30	Weekend	Weekend
0.40	9/4/2022	0	0.40	Weekend	Weekend
0.50	9/5/2022	1	0.06	,=C6*C5*C4	Formula
0.60	9/6/2022	2	0.60
0.70	9/7/2022	3	0.70
0.80	9/8/2022	4	0.80
0.90	9/9/2022	5	0.90
1.00	9/10/2022	6	1.00
1.10	9/11/2022	0	1.10
1.20	9/12/2022	1	1.20
1.30	9/13/2022	2	1.30
1.40	9/14/2022	3	1.40
1.50	9/15/2022	4	1.50
1.60	9/16/2022	5	1.60
1.70	9/17/2022	6	1.70	Weekend	Weekend
1.80	9/18/2022	0	1.80	Weekend	Weekend
1.90	9/19/2022	1	1.90	Holiday
2.00	9/20/2022	2	11.63	=C21*C20*C19*C18	Formula

[ShankerV]

Hi @Jaspal80 

Yes, your requirement is possible.

Can you please confirm whether how are you arriving on the holiday list. 

[Jaspal80]

I am considering UK Holidays

[ShankerV]

Hi @Jaspal80 

Here is the solution to your problem

 

[ShankerV · Accepted solution]

hi @Jaspal80 

Attaching how the solution is arrived for your reference.

We can also increase the WWWO to WWWWO and WWWWWO if there might be 4 days or 5 days leave at a stretch.

Step 1: Converting Weekend and Holiday to W.

Step 2: Doing multirow formula with expression, attached for your reference

Step 3: Calculating the Desired Output using multirow formula with expression, attached for your reference

Step 4: Can be ignored, used select for showcasing the result

 

 

   

[Jaspal80]

Thank you so much champion - This works great. 

you rock :)