Formula for first day of current month

[dhannah]

I am building a table that creates a record for monthly billing. The billing happens on the same day each month. I have a Date Paid field and a Next Date Paid formula field, and an automation that triggers after the bill gets paid to create a new record with the Next Date Paid from the old record populating into the Date Paid field of the new record.

The problem I'm having is with the DateAdd() function. My current formula is:

DATEADD({Date Paid},1,'month')

I thought this would just add one to the month, but it actually just adds 30 days which means that my dates won't match up. I tried adding a field with a static number(MonthDay Paid) and doing something like:

 

DATETIME_FORMAT(DATEADD({Date Paid},1,'month'),'M/{MonthDay Paid}/YY')

but the dynamic slug breaks the DateTime Format function and returns "8/{8thnt121amy Pami4}/24".

I'm trying to automate this as much as possible so that the user doesn't need to really access the data layer much, so any guidance would be very helpful. Thanks!

[dhannah]

A screenshot of the table I'm working with.

[dhannah · Accepted solution]

Well, I did some playing around with timestamps to see if that would change anything, and of course, it changed EVERYTHING.

Turns out my initial assumption that DATEADD([DATE], 1, Month) just adds 30 days was incorrect. The reason I was having a disconnect is because my DatePaid field had no time stamp, and the formula field decided it needed a timestamp, which turned out to be 4pm, so it showed as being the day before it should have. Added a timestamp to the original date field, and now it works fine!

[littleflamingo]

Thanks for posting this - I was just trying to clean up my formatting to remove the timestamps from displaying and it changed all my data. I'm perplexed as to why this would interfere with the formula, but I'm glad I now know to expect it!