Extracting Month from Date in Google Sheets Script

Other Questions Google Sheets
1

Hey everyone! I’m stuck with a Google Sheets script. I’m trying to pull out the month from a cell that has a date in it. The date looks like this: 20/05/2021 (day/month/year).

Here’s what I’ve tried:

let cellDate = sheet.getRange(row, col).getValue();
let dateObj = new Date(cellDate);
let monthNum = dateObj.getMonth();

But it’s not working right. I want monthNum to be 5 for the example date. Any ideas what I’m doing wrong? Is there a better way to get the month from a date string in this format?

I’m pretty new to Google Sheets scripting, so any help would be awesome. Thanks!

2

I’ve faced similar issues when working with dates in Google Sheets scripts. One approach that’s worked well for me is using the built-in Utilities.formatDate() function. It’s really versatile and handles different date formats nicely. Here’s how you could use it:

let cellDate = sheet.getRange(row, col).getValue();
let formattedDate = Utilities.formatDate(new Date(cellDate), 'GMT', 'dd/MM/yyyy');
let monthNum = parseInt(formattedDate.split('/')[1]);

This method first converts your date to a standard format, then extracts the month. It’s been pretty reliable in my experience, especially when dealing with various date inputs. Just remember to adjust the ‘GMT’ part if you’re working with a different timezone. Hope this helps with your script!

3

hey sarahj, the issue is prolly with the date format. Google Sheets might not recognize it correctly. try this instead:

let [day, month, year] = cellDate.split('/');
let monthNum = parseInt(month);

this should give u the month as a number. hope it helps!

4

The problem lies in how Google Sheets interprets the date string. For dd/mm/yyyy format, you’ll need to manually parse it. Here’s a reliable approach:

function getMonth(dateString) {
  var parts = dateString.split('/');
  return parseInt(parts[1], 10);
}

var cellDate = sheet.getRange(row, col).getValue();
var monthNum = getMonth(cellDate);

This function splits the date string and returns the second part (index 1) as an integer. It’s robust and works regardless of whether the date is stored as a string or a date object in the cell. Remember to adjust your script if you need months zero-indexed (0-11 instead of 1-12).