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 |
