Can I add a number of days to a date using built in functions?

[mike13927]

Description

I want to shift a date by a number of days

Example DATEADD(A1, 20) where A1 is a date and '20' is the number of days

I see the built in function ESHIFT adds a number of months and returns a date, but I need to add days to the date

[AMBudnik]

Hi @mike13927

Here's a demo that you can find helpful https://codesandbox.io/s/proud-pine-4eu2g

Used formulas along with their values

[mike13927]

Hi, thank you, that gives me a good idea. In the demo I'm getting a parse error for this cell: "=TEXT(B1, 'MM/DD/YY')"
DetailedCellError {value: "#ERROR!", address: undefined, type: "ERROR", message: "Parsing error.", constructor: Object}, how can I fix that?

[wojciechczerniak]

=TEXT(B1, 'MM/DD/YY') has wrong syntax. Text values should be wrapped with double quotes.
Either '=TEXT(B1, "MM/DD/YY")' or escaped "=TEXT(B1, \"MM/DD/YY\")"

[mike13927]

Thanks, that worked...

One more related question; I can get the detailed type of "NUMBER_DATE", but I want "NUMBER_DATETIME". I'm using the following config options:
parseDateTime: customParseDate,
timeFormats: ['hh:mm', 'hh:mm:ss', 'hh:mm:ss.sss'],
dateFormats: ["YYYY-MM-DD hh:mm:ss"],

With the following Moment helper:
export const customParseDate = (dateString: string, dateFormat: string) => {
const momentDate = moment(dateString, dateFormat, true);

if (momentDate.isValid()) {
let r = {
year: momentDate.year(),
month: momentDate.month() + 1,
day: momentDate.date(),
hour: momentDate.hour(),
minute: momentDate.minute(),
second: momentDate.second()
};
console.log(r)
return r
}
};

/**

• Date formatting function.
• @param {{month: *, year: *, day: *}} dateObject Object with date-related information.
• @returns {string} Formatted date string.
  */
  export const getFormattedDate = (dateStr: string): string => {
  return moment(dateStr).format("YYYY-MM-DD hh:mm:ss");
  };

[wojciechczerniak]

Sorry, I don't understand what you're trying to achieve and where is the problem. Can you provide full working example on codesandbox or similar platform?

[mike13927]

I created this codesandbox with an example: https://codesandbox.io/s/empty-river-yknjv?file=/src/data.js

Please see the output for dates:
Formula Preview | Calculated Value + Detailed type
=DATE(2020, 6, 5) | 43987 : NUMBER_DATE
=TIMEVALUE("10:10:10") | 0.423726851851852 : NUMBER_TIME
=NOW() | 44488.4541782407 : NUMBER_DATETIME
=DATEVALUE("2020:05:05 10:10:10") | #VALUE! : ERROR
=A3 + 2 | 43989 : NUMBER_DATE
=A5 + 2 | 44490.4541782407 : NUMBER_DATETIME

I want to create a NUMBER_DATETIME with an ISO date string like "2020-10-20T05:05:05", but not sure how to do it. Note that I can create a datetime using "NOW".

[wojciechczerniak]

You were close, correct keys are hours, minutes, and seconds

  if (momentDate.isValid()) {
    let r = {
      year: momentDate.year(),
      month: momentDate.month() + 1,
      day: momentDate.date(),
      hours: momentDate.hour(),
      minutes: momentDate.minute(),
      seconds: momentDate.second()
    };

    return r
  }

Also please note that you shouldn't mix date formats with time formats in the configuration. They're both provided ass callback arguments so you can combine them later:

const customParseDate = function(dateString, dateFormat, timeFormat) {
  const momentDate = moment(dateString, `${dateFormat} ${timeFormat}`, true);

And we have to remember that this callback has to recognize DATE, TIME, and DATETIME types. Therefore moment parsing with strict flag should be IMO called for each:

const customParseDate = function(dateString, dateFormat, timeFormat) {
  const momentDateTime = moment(dateString, `${dateFormat} ${timeFormat}`, true);
  if (momentDateTime.isValid()) {
    // handle DATETIME
    return
  }
  const momentTime = moment(dateString, `${timeFormat}`, true);
  if (momentTime.isValid()) {
    // handle TIME
    return
  }
  const momentDate = moment(dateString, `${dateFormat}`, true);
  if (momentDate.isValid()) {
    // handle DATE
    return
  }

Remember that moment supports more than one format:

moment("29-06-1995", ["MM-DD-YYYY", "DD-MM", "DD-MM-YYYY"], strict)

which may be useful to implement more spreadsheet date formats.

[wojciechczerniak]

One thing I'm not sure tho. If ISO string will be recognized as one to go through parseDateTime callback. I think that date and time have to be separated by a whitespace. @izulin, am I right?

[izulin]

One thing I'm not sure tho. If ISO string will be recognized as one to go through parseDateTime callback. I think that date and time have to be separated by a whitespace. @izulin, am I right?

Yes

[wojciechczerniak]

One thing I'm not sure tho. If ISO string will be recognized as one to go through parseDateTime callback. I think that date and time have to be separated by a whitespace. @izulin, am I right?

Yes

Thanks for confirming.

In that case, I don't think parsing ISO format is currently possible. Maybe with #314 we would have a valid solution to this use case. A single callback for string values that returns typed values. Pls track progress in #314