CODEWITHSUNDEEP
google-sheets
JordanBelf
JordanBelf

Reputation: 3338

Formula to get the value for the first day of a month

I am having this issue every time the month changes where I have to modify my Google Spreadsheets to get the value for the first day of the current month from a table. I was wondering if it is possible to have a formula or script to automatically find the value for the first day of the current month.

My data looks like the following:

+---------------------+------+
|        ColA         | ColB |
+---------------------+------+
| 28/10/2012 00:19:01 |   42 |
| 29/10/2012 00:29:01 |  100 |
| 30/10/2012 00:39:01 |   23 |
| 31/10/2012 00:29:01 |    1 |
| 1/11/2012 00:19:01  |   24 |<---
| 2/11/2012 00:19:01  |    4 |
| 3/11/2012 00:19:01  |    2 |
+---------------------+------+

I am pretty sure the function GoogleClock() might help me, but I can't seem to understand how to make it detect the first day.

Any tip will be much appreciated! Thanks!

Upvotes: 2

Views: 3866

Answers (2)

Grae Kindel
Grae Kindel

Reputation: 2684

Seems strange, but the answer lies in EOMONTH.

Example of skipping a month:

   =EOMONTH( "1/1/2000", 0)+1
   "2/1/2000"

You can also do year skipping:

   =EOMONTH( "1/1/2000", 11)+1
   "1/1/2001"

Hope that helps!

UPDATE: Looks like you specifically want EOMONTH( TODAY(), -1)+1]

Upvotes: 3

Jay Walker
Jay Walker

Reputation: 4713

try using a combination of and, today, day, month, and year Here is an example formula: =and(month(A2)=month(today()), year(A2)=year(today()), day(A2)=1)

enter image description here

Upvotes: 0

Related Questions