Formula Help

Nicole Hicks

Summary

Need help developing formula for date range

Content

Hello All,

I am looking for help developing a formula that will help me identify if the date in a column is within 45 days or not within 45 days from the date the report was run.  Any ideas where to start?

 

Sachin Sirohi

Hi Nicole,

I have exactly the same thing in one of my reports. This tells me who all are going to join within 45 days from today. Below is the formula, which will work perfectly for you.

CASE  WHEN TRUNCATE(TIMESTAMPDIFF(SQL_TSI_DAY,CURRENT_DATE, "Submission Dates"."Offer Actual Start Date"),0) <= 45 THEN '45 days or Less'  WHEN TRUNCATE(TIMESTAMPDIFF(SQL_TSI_DAY,CURRENT_DATE, "Submission Dates"."Offer Actual Start Date"),0) > 45 THEN '> 45 days' END

So you just need to replace "Submission Dates"."Offer Actual Start Date" in this formula with your date field. Also, please note this is a future date, so If you want to calculate backwards with past date then you'll have to swap the place of CURRENT_DATE & your date field.

Let me know if run into some issues, I can help you on this.

Thanks,

Sachin

Nicole Hicks

Thank you this is perfect!  Does this formula work on a UDF field that is used to capture a date?  I was able to get the formula to work on the Offer Actual Start Date column but I am not able to get it to work on UDF field we use to capture dates.

Kiran Shenvi

Hi Nicole,

You can use the CAST function to convert the UDF into Date

CAST( UDF AS DATE)

Regards,

Kiran