Calculating Progress of Today's Date

[ah_mktanlys_01]

I am trying to use an equation that tracks the progress of a start and end date based on today's date.

I have used this before =

=MIN((DATEDIF(startdate,today(),"d")+1)/(DATEDIF(startdate,enddate,"d")+1)

but my excel no longer has DATEDIF. I tried inputting the equation anyways and an error shows up.

I then used

=IF(startdate>TODAY(),0,MIN((TODAY()-startdate)/(enddate-startdate),100%))

to get the progress % and to account for if the date has not started yet. However, if I have a start and end date that is only 1 day, I need the equation to show 100% if the 1 day has occurred and 0% if the 1 date has not occurred.

 

[ETAF]

is that because you are ecpecting to see the formula
if you type in
=DATEDIF(A1,TODAY(),"d")
with a date in A1
what happens - do you get NAME error ?

maybe attach a sample spreadsheet

what version of excel are you using

 

[ah_mktanlys_01]

I don't know what I was doing differently before but the DATEDIF equation now works. I think I am good. Thank you!

 

[p45cal]

DATEDIF is still OK in MS365.
It will throw an error if startdate>enddate.
This will cope with start date after today (0%), enddate before today (100%) but not startdate and enddate the same:

=MIN(1,DATEDIF(MIN(TODAY(),StartDate),TODAY(),"d")/DATEDIF(StartDate,EndDate,"d"))

but you don't need to use DATEDIF at all:

=MIN(1,MAX(0,TODAY()-StartDate)/(EndDate-StartDate))

I note there are some +1s dotted here and there, I suspect that's because you might want to include all of the startdate and all of the enddate in the divisor - I'm not sure. You'll have to tweak yourself.