Max date in columm less than or equal to todays date

[PatrickWW]

I used to remember this formula, for some reason I can't remember it. In SQL it's like this

where s.StartDate = (select max(s1.StartDate) from [Server].[dbo].[Table] as s1 where s1.ID = s.ID and s1.CircEff <= getdate () )

Anyone have a solution for this. I am writing this in a PowerPivot calculated field but the logic should be the same.

 

[konew1]

As an array formula: =MAX(IF(A3:A17<+TODAY(),(A3:A17)))
Hold Ctrl+Shift and press Enter.

 

[PatrickWW]

Is it possible to do this for one row. Example: I have a start date and a end date. And I want the max of multiple records or equal to or less than current date.

 

[konew1]

I put my data in A3:A17, but you must change that to wherever your data is located.
If you want to use an entire row then
=MAX(IF(19:19< TODAY(),(19:19)))
My data was in row 19

Note, the "+" sign in my original formula was harmless but redundant

 

[Drrellik]

if you are looking for just the occurrences you can use =countifs along with the Max() as stated above where you find your max date = to or less than current date. and then count the number of times it appears within that range. Ok this is an edit. I think what you are looking for is the date that occurs the most that is no today within a range. for example 6/21 6/21 6/21 6/22 6/22 6/23 you would be looking for 6/21 with a total of 3 times. is occurs the most and is < today 6/24 if this is not right and you are looking for something else please let us know. Don

 

[PatrickWW]

Maybe it's because I am writing this formula in a calculated column in PowerPivot. You are only able to write it on cell in the same row. The table feature auto applies it down the rest of the column. I'm kind of stuck on this one. Thanks for the reply though I do appreciate it. Essentially I have a column full of dates. Some future (forecasted) some now and some recently. I just want to call out the dates that are now or less then. I don't mind an error if the date is after the fact. No worries I may try to do something else with this.

 

[PatrickWW]

You know what would work. If the date was in that range it would flag a 1 but if it was outside the range it would flag a 0. I think that would be the best bet at this point. I could filter out the dates outside the range (forecasted) and just focus on the other piece.