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.

Formula to create sequential numbers based on Date field

Topic Labels: Dates & Timezones Formulas
1234 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Tish_Chambers
4 - Data Explorer
4 - Data Explorer

I am using a Formula field as my Primary Field for a schedule table (each record is a time slot).

Among my formula for naming each time slot, I would like to add a number (ie. 001, 002) which is generated based on the Date field.

ie. Record X: March 5, 2022 = 001, Record Y: May 7, 2022 = 002, Record Z: February 9, 2024 = 003

So, I need a formula that creates a number based on the record's Date field, in relation to the rest of the records.

On top of that, I would like to have separate sequences of numbers based on another field. I don't want all records named in sequential order, I want all Screening (option in single select field named "Type") records named 001>, and all Volunteer Shift records named 001>, etc.

ie. Screening A is named 'Screening 001' (although it may be the 10th record in the sorted-by-date table) and the volunteer shift for that screening might be named 'Volunteer Shift 005' (because there are 4 volunteer shifts scheduled before that one).

(The 001 number format isn't necessary, I can work with 1, 2, etc.)

1 Reply 1

> ie. Record X: March 5, 2022 = 001, Record Y: May 7, 2022 = 002, Record Z: February 9, 2024 = 003

Hm, and so if we created Record A with a date of `June 1, 2022`, it would then become 003 and Record Z would become 004? 

If so, I think you might need to write a script for this