Forum Discussion

Bruno62's avatar
Bruno62
Copper Contributor
Feb 16, 2023
Solved

different dates with TEXT()

Hi, 

 

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 !

 

Bruno

Lists
  • SvenSieverding's avatar
    SvenSieverding
    Feb 19, 2023

    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)

     

    MONTH(created)


     returns the month, so returns a 2.

    TEXT(MONTH(created);"MM")


    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

    TEXT(created;"MM")


    Best Regards,
    Sven

     

  • SvenSieverding's avatar
    SvenSieverding
    Bronze Contributor
    Feb 19, 2023

    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)

     

    MONTH(created)


     returns the month, so returns a 2.

    TEXT(MONTH(created);"MM")


    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

    TEXT(created;"MM")


    Best Regards,
    Sven

     

Resources