Search code examples
excelexcel-formula

I have complex formula in excel and don't know how to sort it out. Can you assist me to fix my below formula?

Here is the time range:

Start Time        End Time
Row L6 9:00:00       Row M6  09:57:15
Row L7 9:57:15       Row M7  10:21:07
Row L8 10:21:07      Row M8  14:48:19
Row L9 14:48:19      Row M9  19:00:00
Row L10 9:00:00      Row M10 13:27:12
Row L11 13:27:12     Row M11 17:37:02
Row L12 17:37:02     Row M12 19:00:00
Row L13 9:00:00      Row M13 09:34:19

Here is the solution that I want:

          START DATE WİTH TİME         END DATE WİTH TİME
Row N6    20.06.2024   9:00:00   Row O6    20.06.2024  09:57:15
Row N7    20.06.2024   9:57:15   Row O7    20.06.2024  10:21:07
Row N8    20.06.2024  10:21:07   Row O8    20.06.2024  14:48:19
Row N9    20.06.2024  14:48:19   Row O9    20.06.2024  19:00:00
Row N10   21.06.2024   9:00:00   Row O10   21.06.2024  13:27:12 
Row N11   21.06.2024  13:27:12   Row O11   21.06.2024  17:37:02
Row N12   21.06.2024  17:37:02   Row O12   21.06.2024  19:00:00 
Row N13   24.06.2024   9:00:00   Row O13   24.06.2024  09:34:19

Start Date is located in cell H5 and has only Date and No Time.
When the time is 19:00:00 in the "End Time" range, you should increment the day by 1 day for the next row day and start from 09:00:00 as mentioned in the Start Time. Saturday and Sunday is weekend. If both dates fall to weekend you should skip 2 days and start always from Monday.
The range to find weekends is:

Row T2:AR2  for the weekday name 
Row T3:AR3  for the weekday date

The weekend is called "HS" and Holiday is "TT".

I tried this formula for the Start Date with Time

=IF(ROW()-ROW($H$5)<=4;TEXT($H$5;"gg.aa.yyyy")&" "&TEXT(INDIRECT("M"&ROW());"ss:dd:nn");
IF(AND(ROW()-ROW($H$5)>=5;ROW()-ROW($H$5)<=8);TEXT(WORKDAY.INTL($H$5;0;"1111100";$T$3:$AR$3)+IF(ROW()-ROW($H$5)=5;1;IF(ROW()-ROW($H$5)=8;4;0));"gg.aa.yyyy")&" "&TEXT(INDIRECT("M"&ROW()-ROW($H$5)+5);"ss:dd:nn");
TEXT(WORKDAY.INTL($H$5;0;"1111100";$T$3:$AR$3)+4;"gg.aa.yyyy")&" "&TEXT($M$20;"ss:dd:nn")))

Everything works in line but when it comes to this point:

21.06.2024 13:27:12
20.06.2024 17:37:02
20.06.2024 19:00:00
24.06.2024 09:34:19

as you can see the dates are not incrementing in line with the date 21.06.2024.
My question is how I can increment the days in line with the previous date.

Solution

  • I wonder whether SCAN would work here:

    1. incr_day_step1 - get increment based on the set time.
    2. incr_day - shift values to apply to the next row.

    Since you're using the default (Saturday and Sunday) we could omit the value; but please add your holiday parameter (I have omitted that - for example date_, TEXT(WORKDAY.INTL(start_date, incr_day,,holidays), "dd.mm.yyyy"))

    Also, adopt the format string and parameter separator (,) to your regional setting.

    Enter the following formula in N6:

    =LAMBDA(start_time, end_time, start_date,
    LET(
        incr_day_step1, SCAN(
            0,
            end_time,
            LAMBDA(acc, cur, IF(cur = TIMEVALUE("19:00:00"), acc + 1, acc))
        ),
        incr_day, VSTACK(0, DROP(incr_day_step1, -1)),
        date_, TEXT(WORKDAY.INTL(start_date, incr_day), "dd.mm.yyyy"),
        start_and_end_times_with_date, HSTACK(
            date_ & " " & TEXT(start_time, "hh:mm:ss"),
            date_ & " " & TEXT(end_time, "hh:mm:ss")
        ),
        start_and_end_times_with_date
    )
)(L6:L13, M6:M13, $H$5)

    Formula with result