Adding a day to a date

1

Hi all

I’m need to get the first and last day of the month, I’m able to get the last day using: EndOfMonth() and I thought it would be easy getting the first day using endofmonth().count()+1.

Here’s my formula: [Date] is a date formatted cell
endOfMonth(Date, -1).Count()+1

But the result I get is: 02/01/1900 which is nowhere near the date I need.

What am I doing wrong?

2

@Juanmata
You can use the results in the formula builder to see what is going on here.
You need just this - endOfMonth(Date, -1) +1
.Count tell you how many items endofmonth function returned ( 1) 1+1 = 2 is formatted as date giving you 02/01/1900.

Screen%20Recording%202019-02-25%20at%2009.25%20PM.gif734×545 2.29 MB

1 Like
3

You can also.use the formula Date.DateTimeTruncate('month') . See https://coda.io/formulas#DateTimeTruncate

4

@mallika

Thanks, that worked perfectly.

@nigel

I thought about DateTimeTruncate but couldn’t get that to work, do you have a working example?

I’ve gone with endofmonth but an example of DateTimeTruncate might benefit others in the future.

Thanks

5

Juan, here’s an example document

  • StartOfMonth: Date.DateTimeTruncate('month')
  • EndOfMonth: Date.DateTimeTruncate('month').RelativeDate(1) - 1

For the end of month, the trick is to use DateRelative to add a month, then subtract a single day to get the month end.

See below for an example doc.

Nigel.