Formating of Excel cell incorrect

%3CLINGO-SUB%20id%3D%22lingo-sub-3278534%22%20slang%3D%22de-DE%22%3EFormating%20of%20Excel%20cell%20incorrect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278534%22%20slang%3D%22de-DE%22%3E%3CP%3EWhen%20I%20create%20a%20horizontal%20sum%20of%20cells%2C%20whose%20contents%20are%20generated%20using%20the%20SUMWHEN%20function%20(in%20german%20its%20SUMMEWENN)%2C%20the%20sum%20is%20fine%20as%20long%20it%20does%20not%20exceed%2024%20h.%20When%20it%20exceeds%2024%20hours%2C%20the%20cell%20displays%20the%20difference%20to%20the%20next%2024%20h.%3C%2FP%3E%3CP%3EFormatting%20the%20summed%20cell%20with%20%5Bhh%5D%3Amm%20does%20not%20help%2C%20the%20results%20gets%20even%20more%20weired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20The%20final%20cell%20H7%20has%20the%20formula%20%3DSUMME(B7%3AG7)%20and%20display%20a%20value%20of%2011.30%20h%2C%20which%20is%20incorrect%2C%20since%20it%20should%20be%20(see%20below)%2059.30%20h%3C%2FP%3E%3CP%3EAll%20cells%20are%20formatted%20as%20Time%2013%3A30%20h.%20When%20I%20format%20cell%20H7%20as%20user%20defined%20as%20%5Bhh%5D%3Amm%2C%20the%20it%20displays%20the%20value%201739%3A30%3C%2FP%3E%3CP%3EB7%20has%20the%20formula%20%3DSUMIF(Wednesday!B5%3AB17%3B%20Staff!A7%3B%20Wednesday!E5%3AE17)%3C%2FP%3E%3CP%3EB7%20displays%20a%20value%20of%200.00%20h%3C%2FP%3E%3CP%3EC7%20has%20the%20formula%20%3DSUMIF(Thursday!%24B%245%3A%24B%2421%3B%20Staff!A7%3BThursday!%24E%245%3A%24E%2421)%3C%2FP%3E%3CP%3EC7%20displays%20a%20value%20of%2010.00%20h%3C%2FP%3E%3CP%3ED7%20has%20the%20formula%20%3DSUMIF(Freitag_Vorb!%24B%245%3A%24B%2425%3B%20Staff!A7%3B%20Freitag_Vorb!%24E%245%3A%24E%2425)%2BSUMIF(Freitag_Fest!%24B%245%3A%24B%2434%3B%20Staff!A7%3B%20Freitag_Fest!%24E%245%3A%24E%2434)%2BSUMIF(Freitag_Fest!%24F%245%3A%24F%2434%3B%20Staff!A7%3B%20Freitag_Fest!%24I%245%3A%24I%2434)%3C%2FP%3E%3CP%3ED7%20displays%20a%20value%20of%2016.30%20h%3C%2FP%3E%3CP%3EE7%20has%20the%20formula%20%3DSUMIF(Saturday!%24B%245%3A%24B%2435%3B%20Staff!A7%3B%20Saturday!%24E%245%3A%24E%2435)%2BSUMIF(Saturday!%24F%245%3A%24F%2435%3B%20Staff!A7%3B%20Saturday!%24I%245%3A%24I%2435)%2BSUMIF(Saturday!%24J%245%3A%24J%2435%3B%20Staff!A7%3B%20Saturday!%24M%245%3A%24M%2435)%3C%2FP%3E%3CP%3EE7%20dislays%20a%20value%20of%2015.30%20h%3C%2FP%3E%3CP%3EF7%20has%20the%20formula%20%3DSUMIF(Sunday!%24B%245%3A%24B%2441%3B%20Staff!A7%3B%20Sunday!%24E%245%3A%24E%2441)%2BTOTALIF(Sunday!%24F%245%3A%24F%2441%3B%20Staff!A7%3B%20Sunday!%24I%245%3A%24I%2441)%2BSUMIF(Sunday!%24J%245%3A%24J%2441%3B%20Staff!A7%3B%20Sunday!%24M%245%3A%24M%2441)%3C%2FP%3E%3CP%3EF7%20displays%20a%20value%20of%2011.30%20h%3C%2FP%3E%3CP%3EG7%20has%20the%20formula%20%3DSUMIF(Monday!%24B%245%3A%24B%2421%3B%20Staff!A7%3B%20Monday!%24E%245%3A%24E%2421)%3C%2FP%3E%3CP%3EG7%20displays%20a%20value%20of%206.00%20h%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3278534%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3278565%22%20slang%3D%22en-US%22%3ERe%3A%20Formating%20of%20Excel%20cell%20incorrect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1356132%22%20target%3D%22_blank%22%3E%40slowfood27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWenn%20ich%20empfehlen%20darf%2C%20f%C3%BCgen%20Sie%20eine%20Datei%20(ohne%20sensible%20Daten)%20ein%2C%20beschreiben%20Sie%20schritt%20f%C3%BCr%20schritt%20Ihr%20Vorhaben%20auf%20Basis%20dieser%20Datei.%20So%20k%C3%B6nnen%20sie%20viel%20schneller%20zu%20einem%20L%C3%B6sungsvorschlag%20kommen.%3C%2FP%3E%3CDIV%20class%3D%22%22%3E(Drag%20and%20drop%20here%20or%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fforums%2Freplypage%2Fboard-id%2FExcelGeneral%2Fmessage-id%2F141473%23%22%20target%3D%22_blank%22%3Ebrowse%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Efiles%20to%20attach%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3EMaximum%20size%3A%2071%20MB%20%E2%80%A2%20Maximum%20attachments%20allowed%3A%205)%3C%2FDIV%3E%3CP%3EDas%20Wissen%20der%20Excel%20Version%2C%20Betriebssystem%20sowie%20Speichermediums%20(Festplatte%2C%20SharePoint%2C%20OneDrive%2C%20etc.)%20w%C3%A4ren%20von%20Vorteil.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHier%20ein%20allgemeiner%20Ansatz%20f%C3%BCr%20nach%20Mitternacht.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Unbenannt.JPG%22%20style%3D%22width%3A%20661px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F362002iAF7F8732378A6C1B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Unbenannt.JPG%22%20alt%3D%22Unbenannt.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHoffe%20das%20ich%20Ihnen%20mit%20dieser%20Information%20ein%20bisschen%20weiterhelfen%20konnte%20%3A).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3278611%22%20slang%3D%22de-DE%22%3ERe%3A%20Formating%20of%20Excel%20cell%20incorrect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278611%22%20slang%3D%22de-DE%22%3EI%20can't%20drop%20my%20generalzed%20xlsx-File%20here%3CBR%20%2F%3EI%20have%20MAC%20OS%20BigSur%20and%20Excel%202016%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

When I create a horizontal sum of cells, whose contents are generated using the SUMWHEN function (in german its SUMMEWENN), the sum is fine as long it does not exceed 24 h. When it exceeds 24 hours, the cell displays the difference to the next 24 h.

Formatting the summed cell with [hh]:mm does not help, the results gets even more weired.

 

Example: The final cell H7 has the formula =SUMME(B7:G7) and display a value of 11.30 h, which is incorrect, since it should be (see below)  59.30 h

All cells are formatted as Time 13:30 h. When I format cell H7 as user defined as [hh]:mm, the it displays the value 1739:30

B7 has the formula =SUMMEWENN(Mittwoch!B5:B17;Personal!A7;Mittwoch!E5:E17)

B7 displays a value of 0.00 h

C7 has the formula =SUMMEWENN(Donnerstag!$B$5:$B$21;Personal!A7;Donnerstag!$E$5:$E$21)

C7 displays a value of 10.00 h

D7 has the formula =SUMMEWENN(Freitag_Vorb!$B$5:$B$25;Personal!A7;Freitag_Vorb!$E$5:$E$25)+SUMMEWENN(Freitag_Fest!$B$5:$B$34;Personal!A7;Freitag_Fest!$E$5:$E$34)+SUMMEWENN(Freitag_Fest!$F$5:$F$34;Personal!A7;Freitag_Fest!$I$5:$I$34)

D7 displays a value of 16.30 h

E7 has the formula =SUMMEWENN(Samstag!$B$5:$B$35;Personal!A7;Samstag!$E$5:$E$35)+SUMMEWENN(Samstag!$F$5:$F$35;Personal!A7;Samstag!$I$5:$I$35)+SUMMEWENN(Samstag!$J$5:$J$35;Personal!A7;Samstag!$M$5:$M$35)

E7 dislays a value of 15.30 h

F7 has the formula =SUMMEWENN(Sonntag!$B$5:$B$41;Personal!A7;Sonntag!$E$5:$E$41)+SUMMEWENN(Sonntag!$F$5:$F$41;Personal!A7;Sonntag!$I$5:$I$41)+SUMMEWENN(Sonntag!$J$5:$J$41;Personal!A7;Sonntag!$M$5:$M$41)

F7 displays a value of 11.30 h

G7 has the formula =SUMMEWENN(Montag!$B$5:$B$21;Personal!A7;Montag!$E$5:$E$21)

G7 displays a value of 6.00 h

 

 

3 Replies

@slowfood27 

Wenn ich empfehlen darf, fügen Sie eine Datei (ohne sensible Daten) ein, beschreiben Sie schritt für schritt Ihr Vorhaben auf Basis dieser Datei. So können sie viel schneller zu einem Lösungsvorschlag kommen.

(Drag and drop here or browse files to attach
Maximum size: 71 MB • Maximum attachments allowed: 5)

Das Wissen der Excel Version, Betriebssystem sowie Speichermediums (Festplatte, SharePoint, OneDrive, etc.) wären von Vorteil.

 

Hier ein allgemeiner Ansatz für nach Mitternacht.

Unbenannt.JPG

Hoffe das ich Ihnen mit dieser Information ein bisschen weiterhelfen konnte :).

 

NikolinoDE

I know I don't know anything (Socrates)

 

I can't drop my generalzed xlsx-File here
I have MAC OS BigSur and Excel 2016
Problem is fixed
ALL Cells involved in the calculation MUST be formatted as [hh]:mm