Date count using DAYS360 does not work on 31/8/2021

Copper Contributor

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

@Jonathan2007 

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 HO

@Jonathan2007 

No, 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.

 

S0725.png

 

I'm afraid I cannot help you with questions about stock tickers etc.