I have a spreadsheet with dates formatted as dd/mm/yyyy and I need to pull out just the month number using Google Apps Script.
Here’s what I’m trying:
var cellDate = new Date(worksheet.getRange(targetRow, targetCol).getValue());
var monthNumber = cellDate.getMonth();
My cell shows 20/05/2021 but something isn’t working right. I want the monthNumber variable to equal 5 for May. What am I doing wrong here? The date looks correct in the spreadsheet but my script isn’t extracting the month properly.
Your issue is that getMonth() uses zero-based indexing, meaning that May returns 4 instead of 5. To resolve this, simply add 1 to the result. Here’s the corrected code:
var cellDate = new Date(worksheet.getRange(targetRow, targetCol).getValue());
var monthNumber = cellDate.getMonth() + 1;
This is a common JavaScript quirk that can confuse many users.
Had this exact problem a few months ago - drove me nuts for hours! charlottew’s right about the zero-based indexing, but there’s another gotcha. Google Sheets might read your dd/mm/yyyy date as mm/dd/yyyy depending on your locale settings. So 20/05/2021 becomes invalid since there’s no 20th month. Add some debugging first - log cellDate.toString() to see if the date’s parsing correctly. If it shows invalid, you’ll need to manually parse the cell value as a string and build the Date object yourself, or just fix your spreadsheet’s locale settings to match your date format.
quick fix: u can use Utilities.formatDate() if the month as a string is all u need. try Utilities.formatDate(cellDate, Session.getScriptTimeZone(), "M"), avoids zero index issues too. also don’t forget to check the date parsing like flyingleaf mentioned.