mysterious bug w/ date formula that is not working

Question

I am trying to recall the end of the month based off a day in a month, however the same formula (and format) is only working in some rows and not in others.&nbsp; Looking for an expert that can help take a look and rectify.&nbsp; Thank you

riny_van_eekelen · Answer

shivom400&nbsp;Please show the formula and the context in which you show it.

shivom400 · Answer

=IFERROR(EOMONTH(DATE(YEAR(K10),DAY(K10),MONTH(K10)),0),"--")for this formula the value in k10 is 01/03/2022, where the format is DD/MM/YYYY

shivom400 · Answer

I'm also using Month() to get the month of 04/03/2022 but it is showing month as 4 despite the date being formatted as DD/MM/2022 and so it should be showing 3

riny_van_eekelen · Answer

shivom400&nbsp;You should be able to use:=EOMONTH(K10,0)provided that the value in K10 is a real date.On your other issue, it seems that your system is set up to work with the mm/dd/yyyy format. Merely changing the display format doesn't change the second element to the month. In other words, a US style date 04/03/2022 = April 3, 2022. You may format it European style to display 03/04/2022, but it will still be 3 April 2022. Hence, the MONTH = 4.&nbsp;

Forum Discussion

mysterious bug w/ date formula that is not working

Share

Resources