# Spot on Year/week\_number(s) calculations

**URL:** <https://community.glideapps.com/t/spot-on-year-week-number-s-calculations/74919>\
**Category:** Ask for Help\
**Created:** [July 15, 2024, 2:09pm UTC](https://community.glideapps.com/t/spot-on-year-week-number-s-calculations/74919 "2024-07-15T14:09:43Z")\
**Posts on this page:** 3\
**Page:** 2

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [July 18, 2024, 1:56pm UTC](https://community.glideapps.com/t/spot-on-year-week-number-s-calculations/74919/21 "2024-07-18T13:56:26Z")

</div>

I have formulas for both methods.

**Given only a date as input:**

- _First day of month_

```auto
Date
-
Day(Date)+1

```

- _Last day of month_

```auto
Date-Day(Date)+45
-
Day(Date-Day(Date)+45)

```

* * *

**Given Year and Month(numeric) as inputs:**

- _First day of month_

```auto
((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_

```auto
((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)

```

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [July 18, 2024, 2:21pm UTC](https://community.glideapps.com/t/spot-on-year-week-number-s-calculations/74919/22 "2024-07-18T14:21:09Z")

</div>

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:

```auto
Year(Date)*10^2 +
Month(Date)

```

---

<div class="post-metadata">

**Author:** ![Andrew\_Davies](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/andrew_davies/32/41838_2.png) [@Andrew\_Davies](https://community.glideapps.com/u/Andrew_Davies)\
**Post date:** [July 18, 2024, 3:01pm UTC](https://community.glideapps.com/t/spot-on-year-week-number-s-calculations/74919/23 "2024-07-18T15:01:57Z")

</div>

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

[Previous page](https://community.glideapps.com/t/spot-on-year-week-number-s-calculations/74919.md?page=1)