Spot on Year/week_number(s) calculations

Ask for Help
21

I have formulas for both methods.

Given only a date as input:

  • First day of month
Date
-
Day(Date)+1
  • Last day of month
Date-Day(Date)+45
-
Day(Date-Day(Date)+45)

Given Year and Month(numeric) as inputs:

  • First day of month
((Now 
+
CEILING((Year-YEAR(Now))*365.2424)
-
DAY(Now)+15)
-
(MONTH(Now)-Month)*30)

-
DAY((Now 
+
CEILING((Year-YEAR(Now))*365.2424)
-
DAY(Now)+15)
-
(MONTH(Now)-Month)*30)

+ 1
  • Last day of month
((Now 
+
CEILING((Year-YEAR(Now))*365.2424)
-
DAY(Now)+15)
-
(MONTH(Now)-Month-1)*30)

-
DAY((Now 
+
CEILING((Year-YEAR(Now))*365.2424)
-
DAY(Now)+15)
-
(MONTH(Now)-Month-1)*30)
2 Likes
22

Just thinking about it some more, you could easily convert dates into YYYYMM and then use that for your query. Basically comparing text or numbers which is easier than comparing dates.

Something like this:

Year(Date)*10^2 +
Month(Date)
2 Likes
23

Yep. That’s the way to do it. I’ll add a column in my transaction table to convert the invoice date to YYYYMM and use that in the query.

Really appreciate your help Jeff. Thanks

2 Likes