Fun with Dates

@Jeff_Hager or @ThinhDinh - having a mental block here…

I’m using the following to get midnight on the first day of the current month:

N+1-DAY(N)-HOUR(N)/24-MINUTE(N)/1440-SECOND(N)/86400

Which works fine, but what I need now is:

  • Midnight on the first day of last month
  • Midnight on the first day of 2 months ago
  • Midnight on the first day of 3 months ago

Example: today is 27th August, I want 3 math columns that will return midnight on the 1st of May, June & July respectively. I’m thinking that the magic number 15 needs to come into play here, but struggling with it…

Help? :pray:

(I know I could do this easily enough with a bit of YC, but prefer not to resort to that unless there is no other option)

1 Like