Count number of days =SUM(History[Evaluation...

(Jungah An) #1

Count number of days

=SUM(History[Evaluation])/NUMBER(DATE(TODAY())-DATE(“1/7/2019”))

SUM(History[Evaluation])=141 NUMBER(DATE(TODAY())-DATE(“1/7/2019”))=0 I want to get ‘29’ as counting the days from 1/7/2019 but it keeps giving me ‘0’

How can I get ‘29’?

(Steven Coile) #2

Subtracting one date from another (e.g., DATE(TODAY())-DATE(“1/7/2019”) produces a Duration value, of the form HH:MM:SS (very similar to–but not!–a Time value). You then need to convert that into a number of days:

NUMBER(HOUR(DATE(TODAY())-DATE(“1/7/2019”)) / 24)

That should get you what you need.

Note, too, that you don’t need NUMBER() (the result will already be a number), DATE() around TODAY() (it’s already a date), or DATE() around “1/7/2019” (it’ll be recognized as a date automatically).

=SUM(History[Evaluation]) / (HOUR(TODAY() - “1/7/2019”) / 24)

(Jungah An) #3

It works perfectly! I will keep that in mind the number 24!! Thanks!