• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

last date before or equal to todays date

G

griffin

New Member
Hello Experts,


Can any one help me as i have a data arranged something like this where the dates are in horizontal manner in a row like 10/1/2012 10/1/2012 12/20/2012 9/20/2013

and i need to find using excel formula the date just equal to todays or before date using now or today formula.pls help


Thanks a lot in advance
 
Hi ,


Will the dates , which are in a row , be in sorted order , either ascending or descending , or will they in random order ?


Narayan
 
Hi ,


Then , a simple HLOOKUP should give you the result :


=HLOOKUP(check_date,dates_range,1,TRUE)


Check_date can have whatever is the date you are looking for ; if you want today's date , it can be TODAY()


dates_range is the row where your dates are arranged in ascending order.


Narayan
 
Hi Narayan,


Thanks for the quick reply but please see the live exampler here i have dates in row2 like this

A2 B2 C2 D2

10/1/2012 10/1/2012 12/20/2012 9/20/2013


now i need to know something like this =max(range(a2:d2)<now())i want to get the date which is equal to todays date or lest than it so if today is 21/11/2012 it should result me in 10/1/2012 from b2 range
 
Hi ,


The formula I posted earlier will return the date 10/1/2012 ; obviously since this date is repeated twice , the first instance in A2 will be the one which is returned. Do you want the second instance to be returned ?


Narayan
 
Hi Narayan,


No i dont need second instant.


Actually i need in range E2 only one result which is 10/1/2012.

A2 B2 C2 D2 E2(After applying formula)

10/1/2012 10/1/2012 12/20/2012 9/20/2013 10/1/2012-only one result


as per yur comment i need to apply formula like HLOOKUP(a2,$a$2:$d$2,1,1) in all cells but i want only one result as shown above.

If possible write the formula as per above scenario pls


Thank you!
 
hi,


you can do =lookup(today(),A2:E2)


or if you put =today() in A1


then you can do =lookup(A1,A2:E2)
 
Hello Koi,


Thanks for the reply, for today its okay but how will you find recent date less than today.


Regards,
 
hi,


i think you didnt try that formula :), if you try then you will understand.


basically lookup will try to lookup value as you wanted, but if it cannot find the lookup value then it will automatically search the lowest value.


a1:a5 = 2, 5, 6, 8, 10


b1: 9


c1: =lookup(b1,a1:a5) ===> result will be 8


why 8? because it cannot find 9 in the range
 
Hi, griffin!

Both NARAYANK991's and koi's formulas work fine.

If you still have any trouble, consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!
 
Hello everyone,


Apologies for late reply and sorry for my misunderstanding the formula.I tried both the

formulas given by Narayan and Koi and both are working as per my need now :).Thanks again for my help.
 
Hi all, I just joined and browsing the forum


Narayan, what formula would you use if the dates were not sorted in any order and you wanted last instance of a date that was used several times?

Thanks, Leeann
 
Hi Leeann ,


It depends on how your data is organized , and whether any additional criteria will be used along with a matching of the date.


Can you explain what exactly your requirement is ? Which columns have what data , and what is to be retrieved using which criteria ?


Narayan
 
Hi leeann,


Try this one:


Code: 
=LOOKUP(2,1/(A1:E1=C6),A2:E2)


or see this file: http://dl.dropbox.com/u/60644346/InverseLookup.xlsx


Regards,
 
Hi Narayan an Faseeh,

If the 'EOM Date' TAB 2 cell value <= 'EOM Date' TAB 1 date AND 'Ref' cell value TAB 2 = 'Ref' TAB 2, return the value from 'Col 2' TAB 1 for the last data that is equal to or less than the date in EOM date


TAB 1

EOM Date REF COL 1 COL 2

30/04/2012 3n18hTYPE23n1811dl 1.26 1.26

31/05/2012 3n18hTYPE23n1811dl 1.20 1.22


TAB 2

EOM REF FROM COL 1 OR COL 2

30/04/2012 3n18hTYPE23n1811dl eg 1.26 should appear

31/08/2012 3n18hTYPE23n1811dl eg 1.22 should appear

30/09/2012 3n18hTYPE23n1811dl eg 1.22 should appear

31/10/2012 3n18hTYPE23n1811dl eg 1.22 should appear


appreciate your help, i started to use count and if but got a little lost
 
Hi leeann,

Not sure if you had the criteria inverted... based on the sample results you indicate, you will need to search for Tab1Dates <= Tab2Date


Using named references:

=INDEX(Tab1Col2, LARGE(IF((Tab1Dates<=A2)*(Tab1Ref=B2), (ROW(Tab1Dates)-ROW(INDEX(Tab1Dates,1))+1)),1))

enter with Ctrl + Shift + Enter


(Here A2 is Tab2EOM row 1, B2 is Tab2Ref row 1, etc.)


On the other hand, if you search for Tab1Dates >= Tab2Date, you could use the following formula:

=INDEX(Tab1Col2, LARGE(IF((Tab1Dates>=A2)*(Tab1Ref=B2), (ROW(Tab1Dates)-ROW(INDEX(Tab1Dates,1))+1)),1))

enter with Ctrl + Shift + Enter

[pre]
Code: 
EOM_Date   REF	                Tab1Dates>=Tab2EOM  Tab1Dates<=Tab2EOM  SampleResult
4/30/2012  3n18hTYPE23n1811dl	  1.22	              1.26	          1.26
8/31/2012  3n18hTYPE23n1811dl	  #NUM!	              1.22	          1.22
9/30/2012  3n18hTYPE23n1811dl	  #NUM!	              1.22	          1.22
10/31/2012 3n18hTYPE23n1811dl	  #NUM!	              1.22	          1.22
[/pre]

The error values indicate cases where the data did not match. You can trap it as desired.


Cheers,

Sajan.
 
thanks Sajan, works well only probelm is I am using the formula nested in a multiple IF statement, calculating the large workbook is really slow - any suggestions
 
Hi leeann,

Not sure what you are doing inside the IF statement...


Please feel free to post the formula you are using, and we might be able to help.


Cheers,

Sajan.
 
Thanks Sajan, Narayan an Faseeh, I cleaned up the workbook and removed unneccessary formulas and all is working really well. Appreciate the quick responses, I will certainly learn alot from this site.
 
You must log in or register to reply here.
Back
Top