Calculating duration in Notion: Handling empty end dates

Other Questions Notion
1

Hey everyone! I’m trying to set up a formula in Notion to figure out how long something’s been going on. I’ve got two date fields: ‘Kickoff’ and ‘Wrap-up’. What I want is to count the days between these dates, but here’s the tricky part - if ‘Wrap-up’ is blank, I need it to count from ‘Kickoff’ to today instead.

I’ve been messing around with formulas and digging through the help sections, but I’m stuck. Here’s what I’ve cobbled together so far:

if(not empty(prop("Wrap-up")), 
   dateBetween(prop("Wrap-up"), prop("Kickoff"), "days"), 
   toNumber(""))

This sort of works, but only when ‘Wrap-up’ has a date. Any ideas on how to make it work with empty end dates too? I’d really appreciate some help on this!

2

hey josephk, i think i got a solution for ya. try this formula:

if(empty(prop(“Wrap-up”)),
dateBetween(now(), prop(“Kickoff”), “days”),
dateBetween(prop(“Wrap-up”), prop(“Kickoff”), “days”))

this should work for both filled and empty wrap-up dates. lemme know if it helps!

3

I’ve dealt with a similar issue in my project management database. Here’s what worked for me:

if(empty(prop("Wrap-up")),
   dateBetween(now(), prop("Kickoff"), "days"),
   dateBetween(prop("Wrap-up"), prop("Kickoff"), "days"))

This formula checks if the Wrap-up date is empty. If it is, it calculates the days between the Kickoff date and today (now()). Otherwise, it uses the Wrap-up date for the calculation.

One thing to keep in mind: this will give you a ‘live’ count that updates daily for ongoing projects. If you need a static number, you might want to consider using a separate property to store the duration once a project is completed.

Hope this helps with your setup!

4

I’ve encountered this issue before in my project tracking system. Here’s a formula that should solve your problem:

dateBetween(if(empty(prop(“Wrap-up”)), now(), prop(“Wrap-up”)), prop(“Kickoff”), “days”)

This approach uses a nested if statement within the dateBetween function. It checks if Wrap-up is empty, and if so, uses the current date (now()). Otherwise, it uses the Wrap-up date.

One advantage of this method is its conciseness. It eliminates the need for an outer if statement, making the formula more streamlined.

Remember to adjust your view settings to update the formula result regularly if you’re tracking ongoing projects.