Timecard - Adding Hours for total

%3CLINGO-SUB%20id%3D%22lingo-sub-2058761%22%20slang%3D%22en-US%22%3ETimecard%20-%20Adding%20Hours%20for%20total%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058761%22%20slang%3D%22en-US%22%3E%3CP%3EHello-%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20sum%20up%20the%20total%20hours%20for%20a%20week%20from%20the%20daily%20-%20and%20a%20basic%20sum%20function%20doesn't%20work.%26nbsp%3B%20I%20have%20attached%20the%20file.%26nbsp%3B%20Can%20anyone%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2058761%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2058842%22%20slang%3D%22en-US%22%3ERe%3A%20Timecard%20-%20Adding%20Hours%20for%20total%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058842%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928310%22%20target%3D%22_blank%22%3E%40StephBarr%3C%2FA%3E%26nbsp%3BHere's%20an%20article%20that%20further%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcalculate-the-difference-between-two-times-e1c78778-749b-49a3-b13e-737715505ff6%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Eexplains%20the%20issue%3C%2FA%3E.%20What%20I%20applied%20to%20the%20yellow%20range%20is%20multiplying%20the%20result%20by%2024%20and%20converting%20it%20to%20a%20Number.%3C%2FP%3E%3CP%3EIn%20regard%20to%20solving%20column%20H%2C%20the%20error%20is%20the%20subtracting%20of%20a%20number%20with%20a%20timeslot.%20If%20you%20populate%20columns%20E%3AG%2C%20a%20simple%20subtraction%20between%20D%20and%20G%20should%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello-

I am trying to sum up the total hours for a week from the daily - and a basic sum function doesn't work.  I have attached the file.  Can anyone help?

 

2 Replies

@StephBarr Here's an article that further explains the issue. What I applied to the yellow range is multiplying the result by 24 and converting it to a Number.

In regard to solving column H, the error is the subtracting of a number with a timeslot. If you populate columns E:G, a simple subtraction between D and G should work.

@StephBarr 

1) For the daily time, use the following formula in H4:

 

=MAX(D4+G4-$I$2,0)

 

2) For the weekly total, use the following formula in H9:

 

=SUM(H4:H8)

 

and apply the custom number format [hh]:mm to H9.