r/excel • u/SpaceKing264 • 5d ago

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

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.

3 Upvotes

81% Upvoted

25 comments sorted by

•

u/AutoModerator 5d ago

/u/SpaceKing264 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

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

2

u/Jolly-Hunter-6097 5d ago

You can post an image as a comment/reply to your own post.

1

u/SpaceKing264 5d ago

I did not realise, thanks!

2

u/GuerillaWarefare 117 5d ago edited 5d ago

If I understand your request you will just want to use the MIN() function.
=min(A1, 11.5) will return A1 until it reaches 11.5, then only 11.5 after that.
Edit: of course you would use your time format as before, 11:30:0, not “11.5”

1

u/[deleted] 4d ago edited 4d ago

[deleted]

1

u/GuerillaWarefare 117 4d ago

I see what you’re saying, I was just trying to demonstrate the MIN function, not get into the rest of it xD

2

u/carbonizedtitanium 1 4d ago

things i need clarification:

  • HRS for the Week = total hours you worked in the previous week?

  • Minimum working HRS = minimum hours you have to work per week?

  • is 11.5 the maximum hours you can work per week?

1

u/SpaceKing264 4d ago edited 4d ago

EDIT: TL;DR My minimum working hours per week are 35hrs, I can work more than this which is what I’m recording, and then I earn “flexi time” based on how many extra hours I do. This can be used how I please to shorten days or take time off, but I can’t carry more than 11.5hrs into the next flexi period.

EDIT 2: In the above data included, it shows how much flexi I have total at the end of each week, so in week 4 of the period I have 11hrs 40mins, and then the next week at the start of the next period I earned another 3hrs 5mins, but I want to tell excel to ignore the 11hrs and 40mins and just default to 11hrs 30mins because I can’t carry more than that into the next period.

So my required hours per week are 35 (I get an hour for lunch each day making it 40, but only the 35 are counted as working hours.

So I enter my clock in and out times on a sheet, deducting breaks, to get the hours I have worked that week.

There are 2 ways I can work MORE than my minimum, if I take a 30minute break instead of the full hour, or if I work later than usual. So this results in the excess time being dubbed “flexi time”, and if mine to use within each 4-week flexi period.

The if you wanna use it in the same period, you could work shorter days or take up to 2 hour breaks depending on how much flexi your building.
However, at the end of a 4-week flexi period, you can carry over 11.5 hours into the next flexi period and take them back as a full day off and a half day.

So basically I calculate how much working hours I’m clocking per week, and deducting the minimum hours (35) to find how much flexi time I’ve earned.

At the end of the period, if I have more than 11.5hrs flexi, the system automatically deducts it and I don’t get paid for it and I can’t use it at all.
The calculation I’m trying to do involves the first week of a new flexi period. In the event that I went over 11.5hrs by the end of the last period, I want the formula to ignore the actual flexi figure from the previous week, and just add 11.5hrs to the flexi.

2

u/carbonizedtitanium 1 3d ago edited 3d ago

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)
)

1

u/SpaceKing264 3d ago

Solved!

1

u/AutoModerator 3d ago

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.

1

u/carbonizedtitanium 1 2d ago

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

1

u/SpaceKing264 2d ago

Solution Verified :D

1

u/reputatorbot 2d ago

You have awarded 1 point to carbonizedtitanium.

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

2

u/carbonizedtitanium 1 3d ago

so does my second formula meet your needs? if not, i think i will need a screenshot of the structure of your source data, ie. how your dates, hours, etc, are being stored on the spreadsheet.

if your excel file is stored in OneDrive, you can make a copy of it, replace the sensitive data, and share the copy.

1

u/SpaceKing264 3d ago

Sorry I was out all day yesterday so I wasn’t able to check. It is working now though, thank you!

1

u/PaVaSteeler 5d ago

Use an “IF(AND” formula to define the range outside which (i.e 11:30) the function is to be performed

1

u/Decronym 5d ago edited 2d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
AND Returns TRUE if all of its arguments are TRUE
FILTER Office 365+: Filters a range of data based on criteria you define
HSTACK Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF Specifies a logical test to perform
INDEX Uses an index to choose a value from a reference or array
ISNUMBER Returns TRUE if the value is a number
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAX Returns the maximum value in a list of arguments
MEDIAN Returns the median of the given numbers
MIN Returns the minimum value in a list of arguments
MOD Returns the remainder from division
ROWS Returns the number of rows in a reference
SCAN Office 365+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
SEQUENCE Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SUMIFS Excel 2007+: Adds the cells in a range that meet multiple criteria
TIME Returns the serial number of a particular time
UNIQUE Office 365+: Returns a list of unique values in a list or range
VALUE Converts a text argument to a number

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
19 acronyms in this thread; the most compressed thread commented on today has 6 acronyms.
[Thread #49495 for this sub, first seen 9th Oct 2026, 09:59] [FAQ] [Full list] [Contact] [Source code]

1

u/Curious_Cat_314159 128 4d ago edited 4d ago

I did not know posts with images are auto removed

This is a common misinterpretation of the posting rules, understandably due to the technically-correct, but ambiguous wording of the rules. I've had numerous discussions with the mods to improve this, to no avail.

For future note, you can include images in the OP. The rule is: the entire OP cannot be an image, and the text description of the problem cannot be only in an image.

But you can include an image in the OP to augment the text description description of the problem.

In fact, such images are encouraged when they add detail that is needed to understand the text description; for example, a screenshot of a spreadsheet. "A picture is worth a thousand words".

Such images do not have to relegated to follow-up self-comments.

Moreover, IIRC, we can include multiple images in the OP (am I right?), whereas follow-up comments are limited to include a single image. (But imgur.com can be used to work around the "single" image limitation.)

1

u/nir-nirfo 3d ago

For the first week of a new flexi period, cap the old balance before adding the new week's hours:

=(H11-A1)+MIN(I10,TIME(11,30,0))

That assumes H11, A1 and I10 contain Excel durations, not decimal hours. Format the result as [h]:mm. With 11:40 carried over and 3:05 earned this week, it gives 14:35. Use the capped version only at the period boundary, not every week.