Issue with particular digits

[Maxime C]

Hi I am having an issue with a fonction that return the difference between a date and the today's date. The result is in month.

My function works well excpet for the date starting by 08 or 09 and I can't explain why... Can you help me ?

The dates I provide are formatted like MM/AA

The result for the dates strating with 08 or 09 are #Number! 

Here is my code : 

function test() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();

  var dataSheet = ss.getSheetByName("DDM en stocks");

  var data = dataSheet.getRange(3,1,1000,2).getValues()

  

  aj=new Date()

  moisAj=aj.getMonth()+1

  anneeAj=aj.getFullYear()*12

  totaj=moisAj+anneeAj

  

  for(i=2;i<data.length+2;i++){

  str=data[i-2][1].split("/")

  mois=parseInt(str[0])

  annee=(parseInt(str[1])+2000)*12

  tot=mois+annee

  dataSheet.getRange(i+1,5).setValue(tot-totaj);}}

Thanks for reading!

[--Hyde]

Hi Maxime,

Are you sure that the dates in the spreadsheet are numeric dates and not text strings that look like dates?

It is unclear why you would need a script for this in the first place. You can get the difference between today and a column of dates with something like this:

  =arrayformula( if( isnumber(B3:B), B3:B - today(), iferror(1/0) ) )

The result will be in days, and you can get it in 30-day "months" with something like this:

  =arrayformula( if( isnumber(B3:B), (B3:B - today()) / 30, iferror(1/0) ) )

If you have no particular reason to use a script rather than a spreadsheet formula, you may want to post your question in the Sheets forum. Remember to attach a sample spreadsheet.

But if you have more complex needs, e.g., need to calculate the number of actual calendar months passed between a date and today, you may want to use a library like moment.js. See Google's Date Add and Subtract for sample code.

Cheers --Hyde

[Bruce Mcpherson]

Hi Maxime

It doesn't work with 8 and 9 because parseInt believes your number is octal (base 8) and 8 and 9 would not be valid digits in base 8. 

A leading zero identifies a string as base 8, so parseInt('09') is not the same as parseInt('9')

Specifically, 

parseInt ('09') is interpreted the same way as parseInt('9', 8) - in the case the base is specified as 8,

whereas parseInt('9') is treated as parseInt(9,10)

also parseInt('0xff') is the same as parseInt('ff', 16) for a hex base.

As a habit, I recommend you always include the base when working with parseInt, as in parseInt(yourString, 10), if you don't expect numbers with other bases to ever be input

Btw - here's a concise version of what you are doing, with a tester for various data types

const months = (a, b) => {
  const da = new Date(a)
  const db = new Date(b)
  return Math.abs(
    12 * (da.getFullYear() - db.getFullYear()) +
    (da.getMonth() - db.getMonth())
  )
}
// like data from a sheet
const data = [
  ['1/1/2001', '1/2/1999'],
  ['1/1/2000', '2/2/2000'],
  [new Date(2002,1,1), '11/10/1987'],
  ['7/11/1921', new Date().getTime()],
  [new Date('1 aug 1934'), new Date('17-sep-1935')]
]
console.log(data.map(f=>months(f[0],f[1])))

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/553e8222-ba72-470a-bffd-1796851bc9e2%40googlegroups.com.

[Maxime C]

Thank you for your advice.

I entered the formulas in google sheet and I refresh it using my script.

it all works!

Best regards - Max