Help

Function counting down the value -1 every one day

Topic Labels: Formulas
515 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Mariusz_Jankows
5 - Automation Enthusiast
5 - Automation Enthusiast

CleanShot 2022-01-12 at 19.53.10@2x

Hi,

I am agonizing over a function that will allow me to subtract a value of -1 every one day from the number in the “Days” column. The function is supposed to start counting when TODAY()>=“Date Start”.

I made such a function in the “Days count” column but it shows the range always smaller by 1(when there are no weekends) and when there are weekends by (-1, -2, -3 etc) depending on how many weekends it has along the way.

Here is my function from the Days count column:
IF(TODAY()>={Date Start},

IF(Weekends="with weekends", DATETIME_DIFF({Date end},TODAY(),'d'),

    IF(Weekends="without weekends", DATETIME_DIFF({Date end},{Date Start},'d')-

        (INT(DATETIME_DIFF({Date end},{Date Start},'d')/7)+

        IF(DATETIME_FORMAT({Date Start},'e')>DATETIME_FORMAT({Date end},'e'),1,0))*2, "")
  ),

“”)

Anyone have any idea how to write such a function that would do this correctly?
The function has to subtract from the value in the Days column the value -1 every day and ideally it should stop at 0 when it finishes basically that’s it.

1 Reply 1
Mariusz_Jankows
5 - Automation Enthusiast
5 - Automation Enthusiast

I don’t believe it, but I think it was enough to just write :winking_face:

IF(TODAY()>={Date Start}, MAX(0, DATETIME_DIFF({Date end},TODAY(),‘d’)))