I’m making a spreadsheet on Google Sheets, and I want to calculate the closest specific date to a calculated date.
So for example, I calculated the date July 8, 2028. I want to round every date onto two specific dates (May 1 and January 1), but the latest one of those from my calculated date. So, July 8, 2028 would round to May 1, 2028, but January 15, 2024 would round to January 1, 2024. And it would always round down, or in this case, round towards the past.
Is there any way I can do this in Google Sheets?
I tried setting the dates as the day number (as in 1 for January 1; 365 for December 31), but that wouldn’t account for the year.
>Solution :
You can do it by checking if the month of the date you need to round down is less than 5 (May). Based on that you can construct either January 1 or May 1 for the year in the original date.
Formula:
=date(year(A2), if(month(A2)<5, 1, 5), 1)
