0

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
  • 33
  • 3
  • 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

5

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,032
  • 3
  • 18
  • 34
Tanaike
  • 139,542
  • 10
  • 71
  • 111
  • 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` – I hope this is helpful to you 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 :) – I hope this is helpful to you 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