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

%3CLINGO-SUB%20id%3D%22lingo-sub-2706896%22%20slang%3D%22en-US%22%3EDate%20count%20using%20DAYS360%20does%20not%20work%20on%2031%2F8%2F2021%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2706896%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20and%20one%20of%20the%20column%20uses%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%24T9%26lt%3B%26gt%3B%22N%22%2CABS(DAYS360(P9%2CU9))%2C0)%20to%20calculate%20the%20number%20of%20days%20should%20column%20N%20is%20a%20%22N%22.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20other%20dates%20work%20well%20except%20when%20it%20comes%20to%20%3CSTRONG%3E%3CU%3E31%2F8%2F2021%3C%2FU%3E%3C%2FSTRONG%3E.%26nbsp%3B%20I%20have%20verified%20my%20FORMAT%20cells%20as%20DATE%20FORMAT%20and%20ABS%20is%20to%20return%20a%20absolute%20value%20regardless%20of%20column%20P%20or%20U%20comes%20first.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20if%26nbsp%3B%20P%20is%2024%2F8%2F2021%20and%20U%20is%2027%2F8%2F2021%2C%20the%20results%20should%20show%203%20days.%3C%2FP%3E%3CP%3EHowever%2C%20when%20P%20is%2030%2F8%2F2021%2C%20and%20U%20is%2031%2F8%2F2021%2C%20the%20result%20show%20is%20O.%20Vice%20versa%20is%20also%20through%20in%20that%20if%20P%20is%2031%2F8%2F2021%20and%20U%20is%2030%2F8%2F2021.%26nbsp%3B%20But%20when%20I%20key%20in%2030%2F8%2F2021%20and%201%2F9%2F2021%2C%20the%20return%20value%20is%201%20between%2030%2F8%2F2021%20and%201%2F8%2F2021%20in%20using%20formula%20%3DDAYS360%3C%2FP%3E%3CP%3ETo%20double%20check%2C%20i%20use%20DAYS(A1%2CB1)%20and%20DAYS360(A1%2CB1)%20to%20confirm%20that%20DAYS360%20does%20not%20recognise%2031%2F8%2F2021%20as%20a%20valid%20entry%20for%20computation%20though%20it%20does%20not%20show%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2706896%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2707057%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20count%20using%20DAYS360%20does%20not%20work%20on%2031%2F8%2F2021%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2707057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1143405%22%20target%3D%22_blank%22%3E%40Jonathan2007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20by%20design.%20DAYS360%20treats%20the%20year%20as%2012%20months%20of%2030%20days.%20The%2031st%20day%20of%20a%20month%20is%20ignored.%3C%2FP%3E%0A%3CP%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fdays360-function-b9a509fd-49ef-407e-94df-0cbda5718c2a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EDAYS360%20function%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2709751%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20count%20using%20DAYS360%20does%20not%20work%20on%2031%2F8%2F2021%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2709751%22%20slang%3D%22en-US%22%3EHi%20Hans%3CBR%20%2F%3E%3CBR%20%2F%3EShould%20not%20then%20be%20358%20days%20since%20there%20are%207%20months%20with%2031st%3F%3CBR%20%2F%3E%3CBR%20%2F%3EAnyway%2C%20my%20purpose%20is%20to%20calculate%20the%20returns%20of%20my%20investments%20in%20tickers%20of%20the%20stock%20markets%20then%20annualised%20such%20a%20returns.%20I%20guess%20it%20can%20be%20complicated%20to%20create%20a%20formula%20for%20each%20trading%20market.%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20do%20you%20recommend%3F%20Is%20there%20such%20formula%20for%20the%20key%20major%20stock%20markets%20already%3F%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20it%20is%20good%20enough.%20I%20will%20do%20exception%20handling%20if%20there%20isn%E2%80%99t.%3CBR%20%2F%3E%3CBR%20%2F%3EJonathan%20HO%3C%2FLINGO-BODY%3E
New 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.