Home

Columns doesnt count right

%3CLINGO-SUB%20id%3D%22lingo-sub-1054358%22%20slang%3D%22en-US%22%3EColumns%20doesnt%20count%20right%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3ESo%20I%20got%20this%20example%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20566px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160561i6F4A90B03377D33F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22missingonemin.PNG%22%20title%3D%22missingonemin.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20green%20value%20should%20show%2001%3A00%3A00%20but%20it%20doesnt.%20I%20think%20the%20formula%20I%20got%20%22ROUND%22%20it%20somehow%20(I%20got%20the%20code%20online).%20And%20I%20need%20this%20code%20so%20the%20value%20can%20exceed%2024%20hours.%20I've%20tried%20to%20remova%20%22ROUND%22%20in%20the%20code%20but%20only%20getting%20syntax%20errors.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EIF(INT(Minuter%2F60)%26lt%3B10%3B%220%22%26amp%3BINT(Minuter%2F60)%3BINT(Minuter%2F60))%26amp%3B%22%3A%22%26amp%3BIF(ROUND((((Minuter%2F60)-INT(Minuter%2F60))*60)%3B0)%26lt%3B10%3B%220%22%26amp%3BROUND((((Minuter%2F60)-INT(Minuter%2F60))*60)%3B0)%3BROUND((((Minuter%2F60)-INT(Minuter%2F60))*60)%3B0))%26amp%3B%22%3A%22%26amp%3BTEXT(%5B%C3%84rende%20p%C3%A5b%C3%B6rjat%5D-%5B%C3%84rende%20inkommet%5D%3B%22ss%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECan%20someone%20help%20me%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1054358%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1084732%22%20slang%3D%22en-US%22%3ERe%3A%20Columns%20doesnt%20count%20right%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1084732%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F446256%22%20target%3D%22_blank%22%3E%40288088%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20the%20below%20syntax%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3DDATEDIF(%5BEff%20Dt%5D%2C%5BExp%20Dt%5D-(MOD(%5BEff%20Dt%5D%2C1)%26gt%3BMOD(%5BExp%20Dt%5D%2C1))%2C%22d%22)%26amp%3B%22%20days%2C%20%22%26amp%3BTEXT(MOD(%5BExp%20Dt%5D-%5BEff%20Dt%5D%2C1)%2C%22hh%20%22%22%20hrs%2C%20%22%22%20mm%20%22%22%20mins%22%22%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%2Cwhere%20%3CSTRONG%3EEff%20Dt%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EExp%20Dt%3C%2FSTRONG%3E%20are%20field%20names.%20Results%20are%20shown%20below%20(%3CSTRONG%3EDiff%3C%2FSTRONG%3E%20field%20-%20calculated%20column)%20as%20an%20example.%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%2F163482i3AFEFF2A2678403D%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%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E
288088
Contributor

So I got this example:

missingonemin.PNG

The green value should show 01:00:00 but it doesnt. I think the formula I got "ROUND" it somehow (I got the code online). And I need this code so the value can exceed 24 hours. I've tried to remova "ROUND" in the code but only getting syntax errors.

 

IF(INT(Minuter/60)<10;"0"&INT(Minuter/60);INT(Minuter/60))&":"&IF(ROUND((((Minuter/60)-INT(Minuter/60))*60);0)<10;"0"&ROUND((((Minuter/60)-INT(Minuter/60))*60);0);ROUND((((Minuter/60)-INT(Minuter/60))*60);0))&":"&TEXT([Ärende påbörjat]-[Ärende inkommet];"ss"))

 

Can someone help me?

1 Reply
Highlighted

@288088 

 

Try the below syntax:

 

=DATEDIF([Eff Dt],[Exp Dt]-(MOD([Eff Dt],1)>MOD([Exp Dt],1)),"d")&" days, "&TEXT(MOD([Exp Dt]-[Eff Dt],1),"hh "" hrs, "" mm "" mins""")

 

,where Eff Dt and Exp Dt are field names. Results are shown below (Diff field - calculated column) as an example.

 

clipboard_image_0.png

 

Hope this helps!

Related Conversations
Counting Days
Tim Hunter in SQL Server on
2 Replies
How to count multiple values in a cell
Ugarte335 in Excel on
7 Replies
Count until
MBelshaw in Excel on
1 Replies
Pivot table
gabriellerocha in Excel on
5 Replies
Taking values from certain rows based on data input
Drakerla in Excel on
5 Replies