Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Re: Formula listing ALL dates between between {Start Date} field & {End Date} field

Solved
Jump to Solution
371 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Nick_Robeson
6 - Interface Innovator
6 - Interface Innovator

Hi all,

Hoping someone can help with a solution! My company has numerous assets that are “active” for long periods of time. I have a formula to calculate the “Active Status” based on the Asset Launch Date & Asset End Date (see below for reference).

Screen Shot 2022-08-26 at 2.18.38 PM

I would like to make an additional field that lists ALL dates an asset is going to be active based on the start and end dates (placeholder included in the image above). For example, if Asset Launch Date = 9/1/22 & Asset End Date = 9/5/22, I’d like the ALL ACTIVE DATES field to read: “9/1/22, 9/2/22, 9/3/22, 9/4/22, 9/5/22”

Note: This field would be hidden 99.9% of the time and ONLY used in an Interface Filter element to allow users to type in specific dates (i.e. 9/3/22) and see the assets that will be “Active” on that date.

If there is another solution you can think of, feel free to leave it here as well!

Thank you in advance!

15 Replies 15

Hi,
I followed another way, it works from {start} to {end} of the current year

IF(AND(start,end), REPLACE(  LEFT(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('g1f1, g1f2, g1f3, g1f4, g1f5, g1f6, g1f7, g1f8, g1f9, g1b0, g1b1, g1b2, g1b3, g1b4, g1b5, g1b6, g1b7, g1b8, g1b9, g1c0, g1c1, g1c2, g1c3, g1c4, g1c5, g1c6, g1c7, g1c8, g1c9, g1-30, g1-31, g2f1, g2f2, g2f3, g2f4, g2f5, g2f6, g2f7, g2f8, g2f9, g2b0, g2b1, g2b2, g2b3, g2b4, g2b5, g2b6, g2b7, g2b8, g2b9, g2c0, g2c1, g2c2, g2c3, g2c4, g2c5, g2c6, g2c7, g2c8, g3f1, g3f2, g3f3, g3f4, g3f5, g3f6, g3f7, g3f8, g3f9, g3b0, g3b1, g3b2, g3b3, g3b4, g3b5, g3b6, g3b7, g3b8, g3b9, g3c0, g3c1, g3c2, g3c3, g3c4, g3c5, g3c6, g3c7, g3c8, g3c9, g3-30, g3-31, g4f1, g4f2, g4f3, g4f4, g4f5, g4f6, g4f7, g4f8, g4f9, g4b0, g4b1, g4b2, g4b3, g4b4, g4b5, g4b6, g4b7, g4b8, g4b9, g4c0, g4c1, g4c2, g4c3, g4c4, g4c5, g4c6, g4c7, g4c8, g4c9, g4-30, g5f1, g5f2, g5f3, g5f4, g5f5, g5f6, g5f7, g5f8, g5f9, g5b0, g5b1, g5b2, g5b3, g5b4, g5b5, g5b6, g5b7, g5b8, g5b9, g5c0, g5c1, g5c2, g5c3, g5c4, g5c5, g5c6, g5c7, g5c8, g5c9, g5-30, g5-31, g6f1, g6f2, g6f3, g6f4, g6f5, g6f6, g6f7, g6f8, g6f9, g6b0, g6b1, g6b2, g6b3, g6b4, g6b5, g6b6, g6b7, g6b8, g6b9, g6c0, g6c1, g6c2, g6c3, g6c4, g6c5, g6c6, g6c7, g6c8, g6c9, g6-30, g7f1, g7f2, g7f3, g7f4, g7f5, g7f6, g7f7, g7f8, g7f9, g7b0, g7b1, g7b2, g7b3, g7b4, g7b5, g7b6, g7b7, g7b8, g7b9, g7c0, g7c1, g7c2, g7c3, g7c4, g7c5, g7c6, g7c7, g7c8, g7c9, g7-30, g7-31, g8f1, g8f2, g8f3, g8f4, g8f5, g8f6, g8f7, g8f8, g8f9, g8b0, g8b1, g8b2, g8b3, g8b4, g8b5, g8b6, g8b7, g8b8, g8b9, g8c0, g8c1, g8c2, g8c3, g8c4, g8c5, g8c6, g8c7, g8c8, g8c9, g8-30, g8-31, g9f1, g9f2, g9f3, g9f4, g9f5, g9f6, g9f7, g9f8, g9f9, g9b0, g9b1, g9b2, g9b3, g9b4, g9b5, g9b6, g9b7, g9b8, g9b9, g9c0, g9c1, g9c2, g9c3, g9c4, g9c5, g9c6, g9c7, g9c8, g9c9, g9-30, i0f1, i0f2, i0f3, i0f4, i0f5, i0f6, i0f7, i0f8, i0f9, i0b0, i0b1, i0b2, i0b3, i0b4, i0b5, i0b6, i0b7, i0b8, i0b9, i0c0, i0c1, i0c2, i0c3, i0c4, i0c5, i0c6, i0c7, i0c8, i0c9, i0-30, i0-31, i1f1, i1f2, i1f3, i1f4, i1f5, i1f6, i1f7, i1f8, i1f9, i1b0, i1b1, i1b2, i1b3, i1b4, i1b5, i1b6, i1b7, i1b8, i1b9, i1c0, i1c1, i1c2, i1c3, i1c4, i1c5, i1c6, i1c7, i1c8, i1c9, i1-30, i2f1, i2f2, i2f3, i2f4, i2f5, i2f6, i2f7, i2f8, i2f9, i2b0, i2b1, i2b2, i2b3, i2b4, i2b5, i2b6, i2b7, i2b8, i2b9, i2c0, i2c1, i2c2, i2c3, i2c4, i2c5, i2c6, i2c7, i2c8, i2c9, i2-30, i2-31', 'g', YEAR(TODAY())&'-0'), 'i', YEAR(TODAY())&'-1'), 'f', '-0'), 'b', '-1'), 'c', '-2'), 9+FIND(DATESTR(end), 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('g1f1, g1f2, g1f3, g1f4, g1f5, g1f6, g1f7, g1f8, g1f9, g1b0, g1b1, g1b2, g1b3, g1b4, g1b5, g1b6, g1b7, g1b8, g1b9, g1c0, g1c1, g1c2, g1c3, g1c4, g1c5, g1c6, g1c7, g1c8, g1c9, g1-30, g1-31, g2f1, g2f2, g2f3, g2f4, g2f5, g2f6, g2f7, g2f8, g2f9, g2b0, g2b1, g2b2, g2b3, g2b4, g2b5, g2b6, g2b7, g2b8, g2b9, g2c0, g2c1, g2c2, g2c3, g2c4, g2c5, g2c6, g2c7, g2c8, g3f1, g3f2, g3f3, g3f4, g3f5, g3f6, g3f7, g3f8, g3f9, g3b0, g3b1, g3b2, g3b3, g3b4, g3b5, g3b6, g3b7, g3b8, g3b9, g3c0, g3c1, g3c2, g3c3, g3c4, g3c5, g3c6, g3c7, g3c8, g3c9, g3-30, g3-31, g4f1, g4f2, g4f3, g4f4, g4f5, g4f6, g4f7, g4f8, g4f9, g4b0, g4b1, g4b2, g4b3, g4b4, g4b5, g4b6, g4b7, g4b8, g4b9, g4c0, g4c1, g4c2, g4c3, g4c4, g4c5, g4c6, g4c7, g4c8, g4c9, g4-30, g5f1, g5f2, g5f3, g5f4, g5f5, g5f6, g5f7, g5f8, g5f9, g5b0, g5b1, g5b2, g5b3, g5b4, g5b5, g5b6, g5b7, g5b8, g5b9, g5c0, g5c1, g5c2, g5c3, g5c4, g5c5, g5c6, g5c7, g5c8, g5c9, g5-30, g5-31, g6f1, g6f2, g6f3, g6f4, g6f5, g6f6, g6f7, g6f8, g6f9, g6b0, g6b1, g6b2, g6b3, g6b4, g6b5, g6b6, g6b7, g6b8, g6b9, g6c0, g6c1, g6c2, g6c3, g6c4, g6c5, g6c6, g6c7, g6c8, g6c9, g6-30, g7f1, g7f2, g7f3, g7f4, g7f5, g7f6, g7f7, g7f8, g7f9, g7b0, g7b1, g7b2, g7b3, g7b4, g7b5, g7b6, g7b7, g7b8, g7b9, g7c0, g7c1, g7c2, g7c3, g7c4, g7c5, g7c6, g7c7, g7c8, g7c9, g7-30, g7-31, g8f1, g8f2, g8f3, g8f4, g8f5, g8f6, g8f7, g8f8, g8f9, g8b0, g8b1, g8b2, g8b3, g8b4, g8b5, g8b6, g8b7, g8b8, g8b9, g8c0, g8c1, g8c2, g8c3, g8c4, g8c5, g8c6, g8c7, g8c8, g8c9, g8-30, g8-31, g9f1, g9f2, g9f3, g9f4, g9f5, g9f6, g9f7, g9f8, g9f9, g9b0, g9b1, g9b2, g9b3, g9b4, g9b5, g9b6, g9b7, g9b8, g9b9, g9c0, g9c1, g9c2, g9c3, g9c4, g9c5, g9c6, g9c7, g9c8, g9c9, g9-30, i0f1, i0f2, i0f3, i0f4, i0f5, i0f6, i0f7, i0f8, i0f9, i0b0, i0b1, i0b2, i0b3, i0b4, i0b5, i0b6, i0b7, i0b8, i0b9, i0c0, i0c1, i0c2, i0c3, i0c4, i0c5, i0c6, i0c7, i0c8, i0c9, i0-30, i0-31, i1f1, i1f2, i1f3, i1f4, i1f5, i1f6, i1f7, i1f8, i1f9, i1b0, i1b1, i1b2, i1b3, i1b4, i1b5, i1b6, i1b7, i1b8, i1b9, i1c0, i1c1, i1c2, i1c3, i1c4, i1c5, i1c6, i1c7, i1c8, i1c9, i1-30, i2f1, i2f2, i2f3, i2f4, i2f5, i2f6, i2f7, i2f8, i2f9, i2b0, i2b1, i2b2, i2b3, i2b4, i2b5, i2b6, i2b7, i2b8, i2b9, i2c0, i2c1, i2c2, i2c3, i2c4, i2c5, i2c6, i2c7, i2c8, i2c9, i2-30, i2-31', 'g', YEAR(TODAY())&'-0'), 'i', YEAR(TODAY())&'-1'), 'f', '-0'), 'b', '-1'), 'c', '-2')
)), 1, -1+FIND(DATESTR(start), 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('g1f1, g1f2, g1f3, g1f4, g1f5, g1f6, g1f7, g1f8, g1f9, g1b0, g1b1, g1b2, g1b3, g1b4, g1b5, g1b6, g1b7, g1b8, g1b9, g1c0, g1c1, g1c2, g1c3, g1c4, g1c5, g1c6, g1c7, g1c8, g1c9, g1-30, g1-31, g2f1, g2f2, g2f3, g2f4, g2f5, g2f6, g2f7, g2f8, g2f9, g2b0, g2b1, g2b2, g2b3, g2b4, g2b5, g2b6, g2b7, g2b8, g2b9, g2c0, g2c1, g2c2, g2c3, g2c4, g2c5, g2c6, g2c7, g2c8, g3f1, g3f2, g3f3, g3f4, g3f5, g3f6, g3f7, g3f8, g3f9, g3b0, g3b1, g3b2, g3b3, g3b4, g3b5, g3b6, g3b7, g3b8, g3b9, g3c0, g3c1, g3c2, g3c3, g3c4, g3c5, g3c6, g3c7, g3c8, g3c9, g3-30, g3-31, g4f1, g4f2, g4f3, g4f4, g4f5, g4f6, g4f7, g4f8, g4f9, g4b0, g4b1, g4b2, g4b3, g4b4, g4b5, g4b6, g4b7, g4b8, g4b9, g4c0, g4c1, g4c2, g4c3, g4c4, g4c5, g4c6, g4c7, g4c8, g4c9, g4-30, g5f1, g5f2, g5f3, g5f4, g5f5, g5f6, g5f7, g5f8, g5f9, g5b0, g5b1, g5b2, g5b3, g5b4, g5b5, g5b6, g5b7, g5b8, g5b9, g5c0, g5c1, g5c2, g5c3, g5c4, g5c5, g5c6, g5c7, g5c8, g5c9, g5-30, g5-31, g6f1, g6f2, g6f3, g6f4, g6f5, g6f6, g6f7, g6f8, g6f9, g6b0, g6b1, g6b2, g6b3, g6b4, g6b5, g6b6, g6b7, g6b8, g6b9, g6c0, g6c1, g6c2, g6c3, g6c4, g6c5, g6c6, g6c7, g6c8, g6c9, g6-30, g7f1, g7f2, g7f3, g7f4, g7f5, g7f6, g7f7, g7f8, g7f9, g7b0, g7b1, g7b2, g7b3, g7b4, g7b5, g7b6, g7b7, g7b8, g7b9, g7c0, g7c1, g7c2, g7c3, g7c4, g7c5, g7c6, g7c7, g7c8, g7c9, g7-30, g7-31, g8f1, g8f2, g8f3, g8f4, g8f5, g8f6, g8f7, g8f8, g8f9, g8b0, g8b1, g8b2, g8b3, g8b4, g8b5, g8b6, g8b7, g8b8, g8b9, g8c0, g8c1, g8c2, g8c3, g8c4, g8c5, g8c6, g8c7, g8c8, g8c9, g8-30, g8-31, g9f1, g9f2, g9f3, g9f4, g9f5, g9f6, g9f7, g9f8, g9f9, g9b0, g9b1, g9b2, g9b3, g9b4, g9b5, g9b6, g9b7, g9b8, g9b9, g9c0, g9c1, g9c2, g9c3, g9c4, g9c5, g9c6, g9c7, g9c8, g9c9, g9-30, i0f1, i0f2, i0f3, i0f4, i0f5, i0f6, i0f7, i0f8, i0f9, i0b0, i0b1, i0b2, i0b3, i0b4, i0b5, i0b6, i0b7, i0b8, i0b9, i0c0, i0c1, i0c2, i0c3, i0c4, i0c5, i0c6, i0c7, i0c8, i0c9, i0-30, i0-31, i1f1, i1f2, i1f3, i1f4, i1f5, i1f6, i1f7, i1f8, i1f9, i1b0, i1b1, i1b2, i1b3, i1b4, i1b5, i1b6, i1b7, i1b8, i1b9, i1c0, i1c1, i1c2, i1c3, i1c4, i1c5, i1c6, i1c7, i1c8, i1c9, i1-30, i2f1, i2f2, i2f3, i2f4, i2f5, i2f6, i2f7, i2f8, i2f9, i2b0, i2b1, i2b2, i2b3, i2b4, i2b5, i2b6, i2b7, i2b8, i2b9, i2c0, i2c1, i2c2, i2c3, i2c4, i2c5, i2c6, i2c7, i2c8, i2c9, i2-30, i2-31', 'g', YEAR(TODAY())&'-0'), 'i', YEAR(TODAY())&'-1'), 'f', '-0'), 'b', '-1'), 'c', '-2')
), ''))

You can save it to csv file and then upload with 'use first row as headers' setting disabled

btw, for anybody interested, I have found how to do a kind of nested loop with regex, so it can be done without huge formula. only current year dates (year of {start}) displayed.

IF(AND(start,end),LEFT( REPLACE(SUBSTITUTE(SUBSTITUTE(REGEX_REPLACE(
REGEX_REPLACE('w01w w02w w03w w04w w05w w06w w07w w08w w09w w10w w11w w12w','w(\\d\\d)w',SUBSTITUTE('x01x02x03x04x05x06x07x08x09x10x11x12x13x14x15x16x17x18x19x20x21x22x23x24x25x26x27x28x29x30x31, ','x', ', '&YEAR(start)&'-'&RIGHT('0'&'$1',2)&'-')),YEAR(start)&'-((0[2469]|11)-31|02-30), ',''),YEAR(start)&'-02-29, ',IF(MOD(YEAR(start),4),'',YEAR(start)&'-02-29, ')),',  , ',', '),1,12*DATETIME_DIFF(start,YEAR(TODAY())&'-01-01','days')+2,''),12*DATETIME_DIFF(end,start,'days')+10))
AndreasG
4 - Data Explorer
4 - Data Explorer

thanks a lot @Alexey_Gusev your formula works like a charm!

But is there any option to make it work for the next year as well? 

Cheers!

I think my explanation was wrong. The formula works well for any year of start/end, as long as they are the same year. If you set end date on the next year from start, it displays dates only till the end of year.
If you need dates from next year, you can try to add &(IF(YEAR(end)=YEAR(start)+1, ... the same formula, adjusted for end.)
Actually the formula displays full list of year dates, 31 day * 12 months,  adjusts months duration, adjust 29.02 when YEAR/4 is integer (therefore it doesn't work correctly for the year 2100, which is not a leap year 🙂  ). Finally, it cuts dates from beginning according to start and cuts dates later than end.   LEFT(REPLACE( & 2 last lines

Alexey_Gusev_0-1732813766741.png

Version more convenient to edit

 

 

IF(AND(start,end),LEFT(
 REPLACE(
  SUBSTITUTE(
     REGEX_REPLACE(
 REGEX_REPLACE('M01MM02MM03MM04MM05MM06MM07MM08MM09MM10MM11MM12M','M(\\d\\d)M', SUBSTITUTE(
  'd01d02d03d04d05d06d07d08d09d10d11d12d13d14d15d16d17d18d19d20d21d22d23d24d25d26d27d28d29d30d31'
 ,'d', ', ' &YEAR(start)&'-'&RIGHT('0'&'$1',2)&'-')), 
      YEAR(start)&'-((0[2469]|11)-31|02-30), ',''),
     YEAR(start)&'-02-29, ',IF(MOD(YEAR(start),4),'',YEAR(start)&'-02-29, ')), 
  1,12*VALUE(DATETIME_FORMAT(start,'DDD'))-10,''),
12*DATETIME_DIFF(end,start,'days')+10))

 

 

The most interesting thing here - using regex_replace to make a kind of loop in formula
Note: sometimes, when copied from a cell (for example I store my scripts and formulas in a dedicated table), the formula can shift few days back for dates starting from March and later. That's because when copying date from older tables, space copied as 'another space'(used to prevent line break in it) with code 120 instead of 32.  To fix you should review formula in the line with two '-02-29, ' and the line above, with regex used to remove 31st from months with less than 31 days,  and put the correct spaces  after comma manually.

Thanks a lot Alexey! That works perfect for my needs and the new formula is also easier to edit and understand. Quite cool that if works with the loops like this!

Much appreciated 🙂