Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Cant get DATEADD to format DD/MM/YYYY correctly

Topic Labels: Base design
2313 3
cancel
Showing results for 
Search instead for 
Did you mean: 
C-B_2021
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

I’m quite new to formulas and I am having a little trouble getting a DATEADD formula to work

I am setting up some formulas for a Page Design invoice and need:
‘Today’s Date’
‘Due Date’ 14 days from ‘Today’s Date’.

My formula for ‘Todays’ Date’ is:
DATETIME_FORMAT(SET_TIMEZONE(TODAY(),‘Perth’),‘DD/MM/YYYY’)

My formula for ‘Due Date’ is:
DATETIME_FORMAT(DATEADD({Today’s Date},14,‘days’),‘DD/MM/YYYY’)

What appears to happen is that DATEADD is adding 14 to the ‘days’ portion of a date (MM/DD/YYYY), but isn’t taking into account the requested DD/MM/YYYY structure until after it has done the adding.

Dateadd

The result as above.

Any help would be great! Thank you.

3 Replies 3
C-B_2021
5 - Automation Enthusiast
5 - Automation Enthusiast

I have found a round-about solution in case anyone comes across this/a better solution isn’t found.

‘Today’s Date Controller’:
TODAY():
+
‘Today’s Date Reformat’:
DATETIME_FORMAT(SET_TIMEZONE({Today’s Date Controller},‘Perth’),‘DD/MM/YYYY’)

&

‘Due Date Controller’:
DATEADD({Today’s Date Controller},14,‘days’)
+
‘Due Date Reformat’:
DATETIME_FORMAT(SET_TIMEZONE(DATEADD({Today’s Date Controller},14,‘days’),‘Perth’),‘DD/MM/YYYY’)

Just an FYI that ‘Perth’ is not a valid time zone. However, ‘Australia/Perth’ is valid. You can see a list of supported timezones here:

You can also get a deep dive into time zones with my training video & sample base here:

C-B_2021
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you for the information! I have now updated the formula with the correct time zone :slightly_smiling_face: