Extracting Month from Date in Google Sheets Script

Other Questions Google Sheets
1

I need help with a Google Sheets script. I’m trying to get the month from a cell that has a date in the format dd/mm/yyyy. Here’s what I’ve tried:

let cellDate = sheetData.getRange(rowNum, colNum).getValue();
let dateObj = new Date(cellDate);
let monthValue = dateObj.getMonth();

But it’s not working right. The cell shows 20/05/2021 and I want monthValue to be 5. Any ideas what I’m doing wrong? I thought this would be simple but I’m stuck. Maybe there’s a problem with how Google Sheets handles dates? Or am I using the wrong method to get the month? Thanks for any help!

2

The issue you’re facing is likely due to how Google Sheets handles dates internally. Instead of using the JavaScript Date object directly, try using the built-in Spreadsheet methods. Here’s a solution that should work:

let cellDate = sheetData.getRange(rowNum, colNum).getValue();
let monthValue = cellDate.getMonth() + 1;

This approach leverages Google Sheets’ native date handling. The getMonth() method in this context returns values from 1-12, so you don’t need to add 1.

If you’re still having trouble, double-check that your date cell is actually formatted as a date in Google Sheets, not just text that looks like a date. You can verify this by applying a different date format to the cell and seeing if it changes appropriately.

3

I ran into a similar issue when working with dates in Google Sheets scripts. The problem is likely that Google Sheets stores dates internally as serial numbers, not as JavaScript Date objects. Here’s what worked for me:

let cellDate = sheetData.getRange(rowNum, colNum).getValue();
let monthValue = cellDate.getMonth() + 1;

This should give you the correct month (5 for May). Remember, getMonth() returns 0-11, so add 1 if you want 1-12.

If that doesn’t work, you might need to use Utilities.formatDate() to ensure proper parsing:

let formattedDate = Utilities.formatDate(cellDate, Session.getScriptTimeZone(), 'MM');
let monthValue = parseInt(formattedDate);

This approach explicitly formats the date to extract the month. Hope this helps!

4

hey tom, i had the same prob before. try this:

let cellDate = sheetData.getRange(rowNum, colNum).getValue();
let monthValue = cellDate.getMonth();

Google Sheets is weird with dates. getMonth() already gives 1-12, so no need to add 1. hope this helps mate!