Days since last sale

[longhorn94]

I have a list of salespeople, and the number of sales each person made each day. Each day of the year is listed chronologically across the top (i.e., Col 1 is Jan 1, Col 2 is Jan 2, etc.). I'm trying to calculate, for each person, the number of days since their last sale, from a given point in time. [SEE ATTACHMENT]

[protonLeah]

O3:

Please Login or Register  to view this content.

[longhorn94]

I got a #VALUE error with this

[FlameRetired]

I got a #VALUE error with this

That is because the "dates" aren't dates. They are text.

If you are not aware of it true dates are numbers ... the number of days since 1/1/1900. In this era they are 5 digit numbers. Ex. Today is 2/12/2018. Excel "sees" 43144. The formatting is for human eyes.

Re enter those as 1/1/2018,1/2/2018, 1/3/2018 ... etc. Format to taste. Then use Ben's formula.

[longhorn94]

I reentered the dates in mm/dd/yyyy format and it still is not working.

[protonLeah]

Sorry, I forgot:
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[longhorn94]

Perfect. Thank you. Can you please just briefly explain what you did with this formula?

[FlameRetired]

Yes. I forgot to mention that as well. My apologies.