Skip to main content

Formula that modifies after a set date

  • April 18, 2022
  • 4 replies
  • 44 views
C
Forum|alt.badge.img+4

Hello everybody,

I have been using a formula to generate invoice numbers, but is has been producing some overly similar looking numbers which confuses clients.

Current formula is:
“INV” & DATETIME_FORMAT(CREATED_TIME(),‘msw’)

What I’d like to change it to is:
“INV” & DATETIME_FORMAT(CREATED_TIME(),‘wms’)

The only problem is that if I change it now, all of my previously issued invoice numbers change too.

Is there a way to perform the below?

If date is on or before 18.04.2022 then
“INV” & DATETIME_FORMAT(CREATED_TIME(),‘msw’)
else
“INV” & DATETIME_FORMAT(CREATED_TIME(),‘wms’)

Your advice would be very helpful! Thank you.

4 replies

V
Forum|alt.badge.img+16
  • Vivid-Squid
  • Inspiring
  • April 18, 2022

Hi @C-B_2021
Can you try this:

IF(CREATED_TIME()<=04/18/2022,"INV" & DATETIME_FORMAT(CREATED_TIME(),'msw'),"INV" & DATETIME_FORMAT(CREATED_TIME(),'wms'))
C
Forum|alt.badge.img+4
  • C-B_2021
  • Author
  • Participating Frequently
  • April 19, 2022

Hi @C-B_2021
Can you try this:

IF(CREATED_TIME()<=04/18/2022,"INV" & DATETIME_FORMAT(CREATED_TIME(),'msw'),"INV" & DATETIME_FORMAT(CREATED_TIME(),'wms'))

Hi, Vivid_Squid. Thank you so much for helping out!

I have input your code and there are no errors, although it appears to skip the If Date < statement, and proceeds to implement ‘wms’ for all date ranges.

Any ideas? Thanks again.

C
Forum|alt.badge.img+4
  • C-B_2021
  • Author
  • Participating Frequently
  • May 3, 2022

Hi @C-B_2021
Can you try this:

IF(CREATED_TIME()<=04/18/2022,"INV" & DATETIME_FORMAT(CREATED_TIME(),'msw'),"INV" & DATETIME_FORMAT(CREATED_TIME(),'wms'))

Sorry @Vivid-Squid, I forgot to tag you!

If you have time, please seem my response above. All the best.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 3, 2022

@C-B_2021

To compare these 2 dates, try this:

IS_BEFORE(CREATED_TIME(),"4/18/2022")

Terms & ConditionsAccessibility statement