SOLVED

Using calculated formulas for time in lists

%3CLINGO-SUB%20id%3D%22lingo-sub-3302049%22%20slang%3D%22en-US%22%3EUsing%20calculated%20formulas%20for%20time%20in%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302049%22%20slang%3D%22en-US%22%3E%3CP%3E%3CBR%20%2F%3EI%20have%20%5BCheck-In%5D%20and%20%5BCheck-Out%5D%20time%20and%20date%20column%20entries%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20then%20have%20a%20%5BTotal%20Time%5D%20calculated%20column%20which%20uses%20this%20formula%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3DTEXT(%5BCheck-In%5D-%5BCheck-Out%5D%2C%22h%3Amm%22)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20can%20get%20the%20total%20time%2C%20however%20I%20need%20to%20be%20able%20to%20set%20an%20IF%20for%20if%20the%20%5BLunch%20Break%5D%20column%20yes%2Fno%20is%20%22Yes%22%20it%20will%20deduct%20-0.5%20or%2030mins%20in%20the%20%5BTotal%20Time%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attempted%20a%20few%20methods%20however%20I%20keep%20getting%20syntax%20or%20incorrect%20values.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3302049%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECalculated%20Column%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormula%20help%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETime%20format%20problem%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETimesheet%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302353%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20calculated%20formulas%20for%20time%20in%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EWow%20thanks%20a%20lot%20Rob%2C%20really%20appreciate%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20managed%20to%20get%20by%20in%20the%20past%20with%20just%20excel%2C%20but%20with%20this%20new%20project%20I%20think%20I%20will%20need%20to%20do%20a%20short%20course%20or%20some%20basic%20training.%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302240%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20calculated%20formulas%20for%20time%20in%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380427%22%20target%3D%22_blank%22%3E%40silaman%3C%2FA%3E%26nbsp%3Bif%20I%20could%20offer%20a%20bit%20of%20advice%20first%3A%20don't%20use%20dashes%20in%20column%20names%20as%20it%20means%20the%20internal%20name%20of%20the%20column%20becomes%26nbsp%3BCheck_x002d_Out%20and%26nbsp%3BCheck_x002d_In%20and%20it%20then%20causes%20problems%20with%20calculated%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20example%20the%20columns%20are%20called%20%3CSTRONG%3ECheckOut%3C%2FSTRONG%3E%20and%20%3CSTRONG%3ECheckIn%3C%2FSTRONG%3E.%20LunchBreak%20is%20a%20Yes%2FNo%20column.%20Adjusted%20is%20the%20calculated%20column%20that%20takes%20off%2030%20minutes%20if%20LunchBreak%20is%20yes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%220-SP-List.png%22%20style%3D%22width%3A%20579px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369083iC4766C42B9F38E83%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%220-SP-List.png%22%20alt%3D%220-SP-List.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20for%20the%20Adjusted%20column%20is%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(LunchBreak%3DTRUE%2CTEXT(CheckIn-CheckOut-(1%2F24%2F60*30)%2C%22h%3Amm%22)%2CTEXT(CheckIn-CheckOut%2C%22h%3Amm%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3329227%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20calculated%20formulas%20for%20time%20in%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3329227%22%20slang%3D%22en-US%22%3EI%20am%20having%20another%20small%20issue%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20working%20over%207.5%20hours%2C%20any%20time%20after%207.5%20hours%20goes%20into%201.5x%20overtime%2C%20and%20then%20any%20time%20after%202%20hours%20of%201.5x%20then%20goes%20into%202.0%20over%20time.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%20work%2011%20hours%20thats%20.5%20off%20for%20lunch%2C%20Id%20do%207.5%20Hours%20of%20standard%20time%20with%202%20hours%20in%201.5x%20and%20then%201%20hour%202.0x%3CBR%20%2F%3E%3CBR%20%2F%3EI%20need%20to%20separate%20these%20times%20in%20each%20column.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20formulas%20aren't%20giving%20me%20the%20right%20data%20and%20I%20can't%20figure%20out%20how%20to%20set%20the%20max%20for%20%5BOverTime1%5D%20to%202%3CBR%20%2F%3E%3CBR%20%2F%3E%5BOverTime1%5D%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(TotalTime%26gt%3B450%2CTEXT(CheckIn-CheckOut-(1%2F24%2F60*450)%2C%22h%3Amm%22)%2CTEXT(0%2C%22h%3Amm%22))%3CBR%20%2F%3E%3CBR%20%2F%3E%5BOverTime2%5D%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(TotalTime%26gt%3B570%2CTEXT(CheckIn-CheckOut-(1%2F24%2F60*570)%2C%22h%3Amm%22)%2CTEXT(0%2C%22h%3Amm%22))%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20been%20trying%20to%20learn%20more%20about%20Syntax%20and%20Logic%3C%2FLINGO-BODY%3E
New 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 (New 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.