SOLVED
Home

Adding hours more than 24

%3CLINGO-SUB%20id%3D%22lingo-sub-1098259%22%20slang%3D%22en-US%22%3EAdding%20hours%20more%20than%2024%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098259%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20I%20am%20still%20busy%20with%20the%20attendance%20register%20and%20need%20to%20solve%20the%20following%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdding%20and%20subtracting%20hours%20in%20the%20same%20formula%2C%20I%20already%20changed%20the%20format%20to%20%5Bh%5D%3Amm%20but%20still%20gets%20the%20%23%23%23%23%23%23%23%2C%20why%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F164428iCDE694F2825858A9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1098259%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1098270%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20hours%20more%20than%2024%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098270%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509992%22%20target%3D%22_blank%22%3E%40Janedb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20doesn't%20show%20negative%20time%2C%20you%20may%20use%20%3DC2-A2-B2.%20If%20you%20need%20both%20positive%20and%20negative%20in%20same%20column%2C%20as%20variant%20you%20may%20transform%20ABS%20to%20text%20with%20adding%20SIGN%3B%20or%20use%20some%20other%20workaround.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1098528%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20hours%20more%20than%2024%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098528%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20sure%20there%20is%20a%20way%20to%20bypass%20the%20negative%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1098557%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20hours%20more%20than%2024%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1098557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20the%20solution....no%20complicated%20formula%20needed.%20Just%20change%20the%20total%20cell%20in%20advance%20settings%20%22%3CSTRONG%3EUse%201904%20date%20system%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F164462i068FE188969FD4A8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi all, I am still busy with the attendance register and need to solve the following formula.

 

Adding and subtracting hours in the same formula, I already changed the format to [h]:mm but still gets the #######, why?

clipboard_image_0.png

3 Replies
Highlighted

@Janedb 

Excel doesn't show negative time, you may use =C2-A2-B2. If you need both positive and negative in same column, as variant you may transform ABS to text with adding SIGN; or use some other workaround.

Highlighted

I am sure there is a way to bypass the negative value.

Highlighted
Solution

@Sergei Baklan 

I found the solution....no complicated formula needed. Just change the total cell in advance settings "Use 1904 date system"

 

clipboard_image_0.png