A common question

Hey ​@Yoli_Fung,

I hope to get back to this tomorrow from my computer. Looks like you might be having an issue with formats being provided, but I should play around with it. 
 

In the meantime, would you mind sharing a screenshot of your table? - just for further context. 
 

Probably less straight forward, but you could always have your projects automatically linked to a Months table, and just count records linked to Feb 25 applying the “Active” condition on a field of type Count. 
 

Mike, Consultant @ Automatic Nation 

Hi ​@Yoli_Fung.

This will flag as active in February (assuming you are counting anything with a start date in February as active):
 

IF(

    AND(

           {Combo Request Date},

           NOT(IS_AFTER({Combo Request Date}, "02-29-2025")),

           OR(

                 NOT({Needed By Date}),

                  NOT(IS_BEFORE({Needed By Date}, "02-01-2025"))

              )

     ),

     1,

     0

)

​​​​I would, as per ​@Mike_AutomaticN’s post, use a separate table for this. You mention this is a common question. I therefore guess a solution that doesn’t involve hard-coding the month in, so as to future-proof yourself, might be preferable.

I’d solve it like this. I have an automation that fires when either the Combo Request Date or Needed By Date fields are updated. But you could use a button instead.

---

Edit: amended the formula to account for empty date fields.

Hi,
This formula, relative to ‘today’, marks projects active in previous month. Means end date in Feb2025 or later or absent In April it will show for March etc
 

IF(DATETIME_FORMAT(start,'YYMM')<DATETIME_FORMAT(TODAY(),'YYMM'),
  IF(DATETIME_FORMAT(end,'YYMM')<
  DATETIME_FORMAT(DATEADD(TODAY(),-1,'month'),'YYMM'),
'old','Active'))

But your question is about total, so it depends on your needs in next months. If you need total for each month, use linked table with months and use formulas with month hardcoded, provided in comments before

Thank you so much for responding back.  ​@Mike_AutomaticN  ​@TimBeeston ​@Alexey_Gusev  I won’t be able to provide a screenshot but can describe what I am working with.  Capturing work involved that span months and displaying that on Interfaces dashboard.  Thank you for offering advice on how to set up the data.  It looks like you all thought of similar ways, get a linked months table to help with the count, write a formula to account for empty cells (Needed By Date), and use an automation to help update the record with the formulas when needed.  I am glad this will help with the clean up process of the dataset that I’m working with.  Hopefully I can share a picture of the end result for you all to see. 

Would love to see that ​@Yoli_Fung. Feel free to reach out with any additional/further questions in the meantime (e.g. how to set up the automation for automatically linking the records of the new table).

You can schedule a brief call using this link!

Mike, Consultant @ Automatic Nation