I have a cell that shows the month “August” as text, and I’m looking for a formula to find the name of the month that comes before it. I attempted using the following formula:
=TEXT(EDATE("August", -1), "MMMM")
Unfortunately, it’s not functioning properly since EDATE requires a correct date format instead of just a month name as text. Can anyone provide guidance on how to convert the month name into a date, subtract a month, and get the previous month’s name? I would greatly appreciate any assistance, as I’ve been confused about this for quite some time.
try this if A1 has the month name: =TEXT(DATE(2024,MATCH(A1,{"January","February","March","April","May","June","July","August","September","October","November","December"},0)-1,1),"MMMM") - it’ll give you the previous month’s name.
Here’s another approach that works well: use MONTH with TEXT and DATE. Extract the month number first, then work with it. Try =TEXT(DATE(2024,MONTH(DATEVALUE(A1&" 1, 2024"))-1,1),"MMMM") - it handles the conversion nicely. You need to create a proper date object before doing month arithmetic. I use this method a lot when month names are inconsistently formatted or when January needs to roll back to December. The DATE function handles those edge cases automatically.
You could also use DATEVALUE with a concatenated date string. Just add a day and year to your month text, then subtract the month. Try =TEXT(EDATE(DATEVALUE(A1&" 1, 2024"),-1),"MMMM") if your month’s in A1. DATEVALUE turns “August 1, 2024” into a real date that EDATE can handle. I’ve done this before with month names in text - works great for year transitions too.
Those formulas work, but they’re a pain to maintain with lots of data transformations.
I’ve hit this same wall with date and text manipulation across hundreds of spreadsheets. Complex Google Sheets formulas break the moment your data structure shifts.
Now I just automate this stuff completely. Build a workflow that grabs your month names, runs them through proper date functions, and spits out results wherever you need them. No more fighting with nested DATEVALUE and EDATE functions.
Connect Google Sheets directly to the automation - when you add new month names, it auto-calculates the previous months. Way cleaner than complex formulas everywhere.
Edge cases like January rolling back to December? The workflow handles it without cramming everything into one monster formula. Just runs in the background and keeps your data fresh.
Check out https://latenode.com for this setup. Beats wrestling with spreadsheet formulas any day.