Help

Reoccurring Date Formula with static Day of the Month

Topic Labels: Formulas
Solved
Jump to Solution
897 2
cancel
Showing results for 
Search instead for 
Did you mean: 
dhannah
5 - Automation Enthusiast
5 - Automation Enthusiast

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!
1 Solution

Accepted Solutions
dhannah
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

See Solution in Thread

2 Replies 2
dhannah
5 - Automation Enthusiast
5 - Automation Enthusiast

A screenshot of the table I'm working with.

 

Finance Screenshot 1.png

dhannah
5 - Automation Enthusiast
5 - Automation Enthusiast

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!