Skip to main content

Cant get DATEADD to format DD/MM/YYYY correctly

  • February 11, 2021
  • 3 replies
  • 68 views
C
Forum|alt.badge.img+4

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.

The result as above.

Any help would be great! Thank you.

3 replies

C
Forum|alt.badge.img+4
  • C-B_2021
  • Author
  • Participating Frequently
  • February 11, 2021

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’)

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • February 11, 2021

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
Forum|alt.badge.img+4
  • C-B_2021
  • Author
  • Participating Frequently
  • February 12, 2021

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’)


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

ScottWorld
Terms & ConditionsAccessibility statement