Forum Discussion
Solved
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.
Modula 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)
Modula 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)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.
