Dec 27 2018 05:03 AM
I created a formula to calculate charges and it's working correctly, but auto sum is not working on these cells. I already checked and the cell format type is General. I have attached the file.
Dec 27 2018 05:35 AM
Format is General, but you return text by your formula
=IF( EXACT(E9,"Not Installed"),"0", IF( (E9>DATEVALUE("1-Oct-18")),"50.00",(DATEDIF(E9,DATEVALUE("31-Oct-18"),"d")+1)*1.61 ))
Use numbers instead
=IF( EXACT(E9,"Not Installed"),0, IF( (E9>DATE(2018,10,1)),50,(DATE(2018,10,31)-E9+1)*1.61 ))
Dec 28 2018 10:10 PM
Solution=IF( EXACT(E9,"Not Installed"),"0", IF( (E9>DATEVALUE("1-Oct-18")),"50.00",(DATEDIF(E9,DATEVALUE("31-Oct-18"),"d")+1)*1.61 ))
had 50 in "" and therefore it was text. Removing double quotes fixed the issue.
=IF( EXACT(E9,"Not Installed"),"0", IF( (E9>DATEVALUE("1-Oct-18")),50.00,(DATEDIF(E9,DATEVALUE("31-Oct-18"),"d")+1)*1.61 ))
Dec 29 2018 02:55 AM
In your updated formula
1) You return zero as text, better as number. Even if that doesn't affect SUM, it affects formatting. Better to have all numbers in resulting column
2) Instead of DATEVALUE with date string better to use DATE or DATEVALUE with reference on another cell. If you open your file on PC with another locale or another default date settings in OS, you formula won't work
3) Not necessary to complicate the formula with DATEDIF to find difference in days. Dates in Excel are just integers, simple subtraction is enough.
Dec 28 2018 10:10 PM
Solution=IF( EXACT(E9,"Not Installed"),"0", IF( (E9>DATEVALUE("1-Oct-18")),"50.00",(DATEDIF(E9,DATEVALUE("31-Oct-18"),"d")+1)*1.61 ))
had 50 in "" and therefore it was text. Removing double quotes fixed the issue.
=IF( EXACT(E9,"Not Installed"),"0", IF( (E9>DATEVALUE("1-Oct-18")),50.00,(DATEDIF(E9,DATEVALUE("31-Oct-18"),"d")+1)*1.61 ))