Skip to main content
added 309 characters in body
Source Link
user1063287
  • 244
  • 11
  • 47

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 the TEXT() 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?

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

Source:  https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

and essentially says:

if (Finish-Start) is greater than 0,
return the TEXT() 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?

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

Sources: 
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 the TEXT() 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?

added 309 characters in body
Source Link
user1063287
  • 244
  • 11
  • 47

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

Source: https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

and essentially says:

if (Finish-Start) is greater than 0,
return the TEXT() 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?

The following seems to produce desired results, but I am hoping it could be made more succinct:

IF(((Finish-(1/24/60*Breaks))-Start)>0, TEXT((Finish-(1/24/60*Breaks))-Start,"h:mm"), "valid Finish time required")

The logic uses the IF() statement:

IF(logical_test, [value_if_true], [value_if_false])

Source: https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

and essentially says:

if (Finish-Start) is greater than 0,
return the TEXT() 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?

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

Source: https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

and essentially says:

if (Finish-Start) is greater than 0,
return the TEXT() 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?

Source Link
user1063287
  • 244
  • 11
  • 47

The following seems to produce desired results, but I am hoping it could be made more succinct:

IF(((Finish-(1/24/60*Breaks))-Start)>0, TEXT((Finish-(1/24/60*Breaks))-Start,"h:mm"), "valid Finish time required")

The logic uses the IF() statement:

IF(logical_test, [value_if_true], [value_if_false])

Source: https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

and essentially says:

if (Finish-Start) is greater than 0,
return the TEXT() 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?