The following seems to produce desired results, but I am hoping it could be made more succinct.
Entries
// representing Start, Finish and Breaks
14/06/2020 9:00 AM , 14/06/2020 12:00 AM , 30
14/06/2020 9:00 AM , 14/06/2020 5:00 PM , 30
14/06/2020 9:00 AM , [ blank ] , [ blank ]
Formula
IF(((Finish-(1/24/60*Breaks))-Start)>0, TEXT((Finish-(1/24/60*Breaks))-Start,"h:mm"), "valid Finish time required")
Output
valid Finish time required
7:30
valid Finish time required
The logic uses the IF()
statement:
IF(logical_test, [value_if_true], [value_if_false])
SourceSources:
https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
https://support.microsoft.com/en-us/office/if-function-7025be14-5665-43d0-af20-8293d1fe9d3a
and essentially says:
if
(Finish-Start)
is greater than 0,
return theTEXT()
formatted result,
otherwise, return an error message
It is performing the evaluation twice though (for the [logical_test]
and [value_if_true]
values).
Can the [logical_test]
value be saved as a variable so that it can be used again as the [value_if_true]
parameter?