Manage time entries based on previous ones

Hello! The use case I am trying to enable is that when a user makes an entry it should not exceed a set cumulative time (eg 120 minutes). So user 1 enters 90 minutes, user 2 enters 40 minutes in the cell below it which exceeds the alloted time by 10 minutes. Can this trigger a warning or validation that prompts for a correction to keep the total time under 120 minutes in this example?

appreciate any tips/advice!

Find more posts tagged with

Formulas

Conditional Formatting

Comments

Zac

So the time that a user is entering, is that like a duration column? Basically they are entering the amount of time it takes them to complete a task? I guess my only thought would be to have two columns, one for duration limit such as your 120 minutes in your example. Then you would have an actual duration column that your users would input their time into.

From here you could just do simple conditional formatting if the actual column is greater than the limit column. Otherwise you could take it one step further and set up a flag or alert column that would essentially flag any row that the actual time was greater than the limit time. In your notifications you could then have an automated email send to either yourself or the assigned to person for the task (if you track that) when this row is flagged.

Tony Pronk

Thanks Zac! let me take a stab along those lines.

J. Craig Williams

Tony,

I'm having a hard time picturing this in a duration colum.

If the limit of 2 hours (120 minutes) is per task set, then you could also use hierarchys where each parent row was the total task and use the =SUM(CHILDREN()) or to get the total entered times and conditional formatting if the value was > 120 minutes.

Or =SUM(CHILDREN(Duration23) for parent row 23 pointing to the Duration column (from another column because Duration can't have a formua if Dependencies are on.

Craig

Tony Pronk

Thanks a lot Craig, I will try (some of) these and let everyone know to close loop.