1

I want to convert a date like this dd/mm/yyyy to numeric format but I don't find any way to do this in google app script For example I have a date in my "L2" cell so I get the value like this :

date = active_sheet.getRange("L2").getValue()

How to convert date variable to numeric format (integer) ? For example today's date in numeric format is something like 44 000

Thanks in advance

Nabs335
  • 125
  • 8
  • In your situation, the value of `date` is the date object? Or it's a string value? – Tanaike Apr 21 '22 at 09:29
  • Hi, I think it's a date object but i'm not sure... The user of my google sheet enter the date manually into the L2 cell so it could be a string ? – Nabs335 Apr 21 '22 at 09:34
  • Thank you for replying. From your reply, I proposed 2 patterns in an answer. Could you please confirm it? If those were not useful and I misunderstood your question, I apologize. – Tanaike Apr 21 '22 at 09:38
  • I just tried it and it works thanks for your help ! – Nabs335 Apr 21 '22 at 09:58
  • Thank you for replying. I noticed that the offset had been never considered. And, the month for converting the string type was not correct. So, I updated my answer. Could you please confirm it? – Tanaike Apr 21 '22 at 12:10

1 Answers1

7

I believe you want to convert the date object or the date string to the serial number using Google Apps Script.

From "I think it's a date object but I'm not sure", I think that 2 patterns can be considered.

Pattern 1:

In this pattern, it supposes that the value of date = active_sheet.getRange("L2").getValue() is the date object. The sample script is as follows.

var active_sheet = SpreadsheetApp.getActiveSheet();
var date = active_sheet.getRange("L2").getValue();
var serialNumber = (new Date(date.getTime() - (1000 * 60 * date.getTimezoneOffset())).getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953
console.log(serialNumber);

Pattern 2:

In this pattern, it supposes that the value of date = active_sheet.getRange("L2").getValue() is the string value like dd/mm/yyyy. The sample script is as follows.

var active_sheet = SpreadsheetApp.getActiveSheet();
var date = active_sheet.getRange("L2").getValue(); // or getDisplayValue()
var [d, m, y] = date.split("/");
var dateObj = new Date(y, m - 1, d);
var serialNumber = (new Date(dateObj.getTime() - (1000 * 60 * dateObj.getTimezoneOffset())).getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953
console.log(serialNumber);

Testing:

For both of the above scripts, when a sample value of 21/04/2022 is used, 44672 is returned.

Jeff Schaller
  • 2,352
  • 5
  • 23
  • 38
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Why does the value end in `.625`? if it's a day serial? My understanding is this number is equal to the number of whole days since `31/12/1899` – Rafa Guillermo Apr 21 '22 at 10:14
  • 1
    @I hope this is helpful to you Thank you for your comment. About `Why does the value end in .625?`, in this case, I think that the reason of this is due to that the offset is not used. So, I included the offset in the script. Could you please confirm it? In this case, `21/04/2022` returns `44672`. – Tanaike Apr 21 '22 at 12:09
  • 1
    I can confirm this, thank you for clearing it up :) – Rafa Guillermo Apr 21 '22 at 13:57
  • @I hope this is helpful to you Thank you for replying and testing it! – Tanaike Apr 21 '22 at 23:16