IF - Duration of Project

Platform discussions Special workflows & use cases
,
1

Hello! I need help with a formula that I can’t for the life in me hack it.

I want a formula for duration with a few variables.

DURATION from “Start Date” to “Today”, HOWEVER, IF I set an “End Date”, THEN DURATION from “Start Date” to “End Date”.

Could someone please help me out?

2

Hello @RobyMarcondes ,

Welcome to the monday community!

Please try this :

CONCATENATE(
(ROUNDDOWN((DAYS(IF({End Date},{End Date},TODAY()),{Start Date})/30.4)/12,0))," years ",
(ROUNDDOWN((((DAYS(IF({End Date},{End Date},TODAY()),{Start Date})/30.4)/12)-(ROUNDDOWN((DAYS(IF({End Date},{End Date},TODAY()),{Start Date})/30.4)/12,0)))*12,0))," months ",
IF(DAY({Start Date}) <= DAY(IF({End Date},{End Date},TODAY())), 
   DAY(IF({End Date},{End Date},TODAY())) - DAY({Start Date}) + 1, 
   DAY(IF({End Date},{End Date},TODAY())) + DAY(EOMONTH({Start Date}, 0)) - DAY({Start Date}) + 1), 
" days")

When you input a start date ,it calculates the end date as today if you have not chosen one, if you have it calculates the distance between the dates.

**Note, if you put a future start date with no end date the formula does not work

image

Let me know if this helps!

Best Regards,
Giannis, Implementation Consultant at thespelas.com

1 Like
4

Hello Giannis! Thank you so muuuuch! It worked!!! :smiley:

1 Like
5

Hello Giannis,

Since you are clearly a magician with these formulas, I was wondering if you’d have the answer to another.

What formula could I use to calculate how many days I have left or gone over my due date, but only if there is a due date, otherwise it would be empty.

TODAY - DUE DATE = X, or -X, BUT IF no DUE DATE = n/a

1 Like
6

Thank you for the kind words @RobyMarcondes , try this :

IF({Due Date}, ROUND(DAYS({Due Date}, TODAY()), 0), "No Due Date Chosen")

image

Note : The formula does not take today’s day into consideration. You might prefer to add +1 after TODAY()) in the formula (… TODAY())+1,0…) to resolve that, but then you will get an error if you have chosen no Due Date. Please adjust according to your preferences.

Let me know if this helps :slight_smile: .

Giannis, Implementation Consultant at thespelas.com

1 Like
7

PERFECT! Thank you once again! (:smiley: