different dates with TEXT()

Copper Contributor



i have some problems with date in a List on Sharepoint

i would like use the date to make an order number like : YYYYMMDD-XX : XX is a number from 0 to X. i would like to reset this number everyday


i have this problem: 

when i have the formula : MONTH(created), i obtain "2", ok for Februar but when i write: TEXT(MONTH(created);"MM"), i obtain "1"


and a question: how can i do to reset my number everyday... 


Thanks !



2 Replies

Hello @Bruno62 


that's the one i found on :


Best, Dave

best response confirmed by Bruno62 (Copper Contributor)

Hi @Bruno62,

this is complicated and will not work with a calculated formula alone. You have to create your own "Counter" Logic if you basically want to count all elements that have been created today

I would use a Power Automate flow

  • That triggers when an item in that list is created
  • That then retrieves all items from that list that have been created today
  • Counts them
  • Adds 1
  • Updates this item and sets a textfield to "concat(formatDateTime(utcNow(),'YYYYMMDD'),'-',@ListItemCountFromPrevious)"

For the formatting of the dates (Assuming "created" was a date in february)



 returns the month, so returns a 2.


first calculates the month of the created date as a "2" and then takes that "2" and tries to formats it using the "MM" date format. This is a wierd syntax as MONTH(created) does not return a date.

Just use this formula instead to get the month part of a date with leading zeros


Best Regards,