SOLVED

Using calculated formulas for time in lists

Copper Contributor


I have [Check-In] and [Check-Out] time and date column entries

I then have a [Total Time] calculated column which uses this formula 

=TEXT([Check-In]-[Check-Out],"h:mm")

I can get the total time, however I need to be able to set an IF for if the [Lunch Break] column yes/no is "Yes" it will deduct -0.5 or 30mins in the [Total Time]

 

I have attempted a few methods however I keep getting syntax or incorrect values.

2 Replies
best response confirmed by silaman (Copper Contributor)
Solution

@silaman if I could offer a bit of advice first: don't use dashes in column names as it means the internal name of the column becomes Check_x002d_Out and Check_x002d_In and it then causes problems with calculated columns.

 

In my example the columns are called CheckOut and CheckIn. LunchBreak is a Yes/No column. Adjusted is the calculated column that takes off 30 minutes if LunchBreak is yes.

 

0-SP-List.png

 

The formula for the Adjusted column is:

=IF(LunchBreak=TRUE,TEXT(CheckIn-CheckOut-(1/24/60*30),"h:mm"),TEXT(CheckIn-CheckOut,"h:mm"))

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

@RobElliott

Wow thanks a lot Rob, really appreciate it.

I've managed to get by in the past with just excel, but with this new project I think I will need to do a short course or some basic training.
 

1 best response

Accepted Solutions
best response confirmed by silaman (Copper Contributor)
Solution

@silaman if I could offer a bit of advice first: don't use dashes in column names as it means the internal name of the column becomes Check_x002d_Out and Check_x002d_In and it then causes problems with calculated columns.

 

In my example the columns are called CheckOut and CheckIn. LunchBreak is a Yes/No column. Adjusted is the calculated column that takes off 30 minutes if LunchBreak is yes.

 

0-SP-List.png

 

The formula for the Adjusted column is:

=IF(LunchBreak=TRUE,TEXT(CheckIn-CheckOut-(1/24/60*30),"h:mm"),TEXT(CheckIn-CheckOut,"h:mm"))

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

View solution in original post