Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Formula to create sequential numbers based on Date field

Topic Labels: Dates & Timezones Formulas
939 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