Forum Discussion
Formula Help please
Hi
In my spreadsheet I have 2 columns that calculate the number of days (columns S and T) - they are both using column F to calculate the number of days (see screen shot).
How can I stop the formula returning random values (highlighted in red) when column F is empty? I want to report on these columns (S thru to V) but it's throwing my reports way out because of the random values.
Many thanks
Nicki,
You have to turn off the formulas in the red area when the reference in column F is empty as below:
Replace the formula in cell S5 with this:
=IF(F5="","",DAYS360(F5,DATE(2018,7,31)+1))
And apply this workaround =IF(F5="","",ORIGINAL_FORMULA) to other columns the red area.
- Thank you Haytham! Works a charm!
- Sorry - another question
How do I turn this around to show a positive outcome (currently showing me a negative)? I want to calculate the number of days from 1,8,2018 until their departure date (G5)
=IF(G5="","",DAYS360(G5,DATE(2018,8,1)-2))
G5 in this example is 05/08/2018 so should be 5 days (inc the 1st Aug)
ThanksReverse it this way:
=IF(G5="","",DAYS360(DATE(2018,8,1)-2,G5))
Or use this instead:
=ABS(IF(G5="","",DAYS360(G5,DATE(2018,8,1)-2)))
