Hi. I’m looking for a formula that will give me the maximum working hours based on a start time entered in a previous cell.
eg. If an employee starts work at 07.40am and the maximum they can work is 13 hours if they start between 06.00-13.29, how do I get 13 hours to display in the next cell.
I *think* what you're saying is that for any given time value, if it is between 06:00 and 13:29 you want to return "13 Hours" (a text value?)... is that right?
what happens if they start earlier or later? the cell should be empty?
Given some assumptions, here's one approach to consider. For the date/time value in cell A1, use the formula:
=IFS(TIMEVALUE(A2)<0.25,"EARLY",
TIMEVALUE(A2)≥ 0.5625,"LATE",
TRUE,"13 hours")
This uses the IFS() function to run a series of comparisons. The first one that passes (is TRUE) is the winner. In this case it starts by checking the TIMEVALUE() of cell A2 is less than 0.25 (TIMEVALUE() returns a decimal value representing the elapsed part of the day, so 6 hours is 0.25 of 24 hours). If that checks out, the formula returns "EARLY".
If that doesn't check out, IFS() then checks if the TIMEVALUE() A2 is greater than or equal to 0.5625 (which represents the decimal fraction of 13:30 over 24 hours). If this passes, the function returns "LATE".
If neither of the above checks pass we know the time must be between those two values, so we just force TRUE() and return the string "13 hours".
Any of the string values can be adjusted, or empty strings if you want no value returned.
I *think* what you're saying is that for any given time value, if it is between 06:00 and 13:29 you want to return "13 Hours" (a text value?)... is that right?
what happens if they start earlier or later? the cell should be empty?
Given some assumptions, here's one approach to consider. For the date/time value in cell A1, use the formula:
=IFS(TIMEVALUE(A2)<0.25,"EARLY",
TIMEVALUE(A2)≥ 0.5625,"LATE",
TRUE,"13 hours")
This uses the IFS() function to run a series of comparisons. The first one that passes (is TRUE) is the winner. In this case it starts by checking the TIMEVALUE() of cell A2 is less than 0.25 (TIMEVALUE() returns a decimal value representing the elapsed part of the day, so 6 hours is 0.25 of 24 hours). If that checks out, the formula returns "EARLY".
If that doesn't check out, IFS() then checks if the TIMEVALUE() A2 is greater than or equal to 0.5625 (which represents the decimal fraction of 13:30 over 24 hours). If this passes, the function returns "LATE".
If neither of the above checks pass we know the time must be between those two values, so we just force TRUE() and return the string "13 hours".
Any of the string values can be adjusted, or empty strings if you want no value returned.
Formula for duration answer based on time range