SOLVED

# Using calculated formulas for time in lists

Copper Contributor

# Using calculated formulas for time in lists

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

# Re: Using calculated formulas for time in lists

@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.

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)

# Re: Using calculated formulas for time in lists

@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

# Re: Using calculated formulas for time in lists

@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.

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)