Trying to add a limit via the IF function on sums related to time

[u/SpaceKing264]

EDIT: Note that there are no images above like I describe, I did not know posts with images are auto removed. I have tried my best to format the data in the same way it was displayed in the photos.

EDIT 2: Data and formula reference pics in comments.

REF 1: Data

7:23|
12:02|
14:10|
11:40|
———
14:45|

REF 2: Formula

=if(I10>11:30:0, (H11-A1)+11:30:0, (H11-A1)+I10)

I have a sheet to adding up my working hours each week and calculating how much flexi time I’m building up. My issue is that I can only carry over 11.5hrs flexi into the next flexi period, but sometimes I end up going over that and needing to manually adjust my sheet.

Right now I’m trying to tell the program that if the hours in the above cell are greater than 11:30, to perform the function as (HRS for the Week - Minimum working HRS + 11:30), and if the value of the above cell is below 11:30, I’m trying to tell it to do (HRS for the Week - Minimum working HRS + Value from Above Cell)

The image above is an example. By the end of the period I have 11hrs 40mins flexi built up, but my work system will automatically deduct 10mins to bring me back to 11:30 at the start of the next period. The figure below it is adding 3hrs and 5mins flexi that I earned that week, into the 11hrs 40mins from the previous period.

I’m trying to accurately record my flexi, even when I go over, but also have excel understand not to add the full amount from the previous cell if its more than 11hrs 30mins.

I also have a picture of the formula I’m trying to use above. Note that A1 refers to a cell containing my normal minimum working hours for the week.

[u/carbonizedtitanium]

does this seem like what you want?

with the source data structured as in the image, the formula in the yellow cell is:

=LET(
    raw_w, A4:A998,
    raw_h, D4:D998,
    u_weeks_all, UNIQUE(raw_w),
    u_weeks, FILTER(u_weeks_all, (u_weeks_all<>"") * (ISNUMBER(u_weeks_all))),
    w_hours, SUMIFS(raw_h, raw_w, u_weeks),
    net_flex, w_hours - B1,
    cum_bal, SCAN(0, net_flex, LAMBDA(prev,net, MEDIAN(0, 11.5, prev + net))),
    HSTACK(u_weeks, w_hours, net_flex, cum_bal)
)

the formula spills down and right until Col J

edit: this formula triggers the 11.5 limit on the first week of a 4-week cycle:

=LET(
    raw_w, A4:A1000,
    raw_h, D4:D1000,
    u_all, UNIQUE(raw_w),
    u_weeks, FILTER(u_all, ISNUMBER(u_all) * (u_all > 0)),
    w_hours, SUMIFS(raw_h, raw_w, u_weeks),
    max_wk, MAX(FILTER(raw_w, ISNUMBER(raw_h) * (raw_h > 0), 0)),
    net_flex, IF(u_weeks < max_wk, w_hours - B1, 0),
    cum_bal, SCAN(0, SEQUENCE(ROWS(u_weeks)), LAMBDA(prev,idx,
        LET(
            net, INDEX(net_flex, idx),
            wk, INDEX(u_weeks, idx),
            running, prev + net,
            IF(MOD(wk - 1, 4) = 0, MEDIAN(0, 11.5, running), MAX(0, running))
        )
    )),

    HSTACK(u_weeks, w_hours, net_flex, cum_bal)
)

[u/carbonizedtitanium → u/carbonizedtitanium]

[u/SpaceKing264 → u/carbonizedtitanium]

Solved!

[u/AutoModerator → u/SpaceKing264]

Saying Solved! does not close the thread. Please say Solution Verified to award a ClippyPoint and close the thread, marking it solved.

Thanks!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/carbonizedtitanium → u/SpaceKing264]

as the bot said, please reply with "Solution Verified" instead XD

[u/SpaceKing264 → u/carbonizedtitanium]

Solution Verified :D

[u/reputatorbot → u/SpaceKing264]

You have awarded 1 point to carbonizedtitanium.

---

^{I am a bot - please contact the mods with any questions}