I’m working with Google Sheets and need help with a date formula. I want to automatically calculate the first day of the current month based on today’s date. For example, if today is February 11th 2015, I need the formula to give me February 1st 2015. This is for creating monthly reports that update automatically. Right now I’m manually entering dates but I need something that works every month without changing it. Can someone share a formula that uses Google Sheets date functions to do this?
I always use =DATE(YEAR(TODAY()),MONTH(TODAY()),1). It’s way more explicit than other methods - you can see exactly what you’re getting: current year, current month, day 1. No confusing math that’ll trip up whoever looks at your spreadsheet later. Been using this in financial dashboards for three years without issues. Plus it’s super easy to tweak - need next month? Just add +1 to the MONTH() part. Perfect for automated monthly reports where you need a clean reference point that updates itself.
Here’s the formula you need:
=EOMONTH(TODAY(),-1)+1
This grabs today’s date, finds the end of last month, then adds 1 day to get the first day of this month.
Or you can use:
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
This just builds the date with the current year and month, but sets the day to 1.
Both update automatically when the month rolls over, so your reports always have the right start date.
But if you’re building monthly reports, you’re probably doing tons of manual stuff beyond just the date formula - pulling data, formatting it, sending it out.
I’ve automated entire reporting workflows with Latenode. It grabs data from Google Sheets, processes it, creates formatted reports, and emails them to stakeholders automatically. Way better than having the right date but still doing everything else manually.
Check it out: https://latenode.com
You can also use =TODAY()-DAY(TODAY())+1. This formula grabs today’s date, subtracts the current day number, then adds 1. So if today’s February 11th, it subtracts 11 days to get January 31st, then adds 1 day to get February 1st. I’ve used this method for two years in my expense tracking sheets and it works great. What I like about it is you can actually see what’s happening - you’re going back to day 1 of the current month. EOMONTH confuses some people because of that -1 parameter, but this way is more straightforward.