Forum Discussion
Date count using DAYS360 does not work on 31/8/2021
I have a spreadsheet and one of the column uses
=IF($T9<>"N",ABS(DAYS360(P9,U9)),0) to calculate the number of days should column N is a "N".
All other dates work well except when it comes to 31/8/2021. I have verified my FORMAT cells as DATE FORMAT and ABS is to return a absolute value regardless of column P or U comes first.
For example if P is 24/8/2021 and U is 27/8/2021, the results should show 3 days.
However, when P is 30/8/2021, and U is 31/8/2021, the result show is O. Vice versa is also through in that if P is 31/8/2021 and U is 30/8/2021. But when I key in 30/8/2021 and 1/9/2021, the return value is 1 between 30/8/2021 and 1/8/2021 in using formula =DAYS360
To double check, i use DAYS(A1,B1) and DAYS360(A1,B1) to confirm that DAYS360 does not recognise 31/8/2021 as a valid entry for computation though it does not show error.
3 Replies
This is by design. DAYS360 treats the year as 12 months of 30 days. The 31st day of a month is ignored.
See DAYS360 function
- Hi Hans
Should not then be 358 days since there are 7 months with 31st?
Anyway, my purpose is to calculate the returns of my investments in tickers of the stock markets then annualised such a returns. I guess it can be complicated to create a formula for each trading market.
What do you recommend? Is there such formula for the key major stock markets already?
But it is good enough. I will do exception handling if there isn’t.
Jonathan HONo, it's really 360. All months are treated as if they have 30 days, even February.
A1 = February 27, 2021
B1 = March 1, 2021
=DAYS(A1,B1) returns 4, as if February had 30 days too.
If you want to know the real difference in days, use =DAYS(B1,A1) or simply =B1-A1.
I'm afraid I cannot help you with questions about stock tickers etc.
