Getting the Earliest Date on a Selection with Today() as Reference

[bye]

Hi Excel Gurus,

May I ask for your help, please?
I am currently using MS Excel 2007 and aiming to add a formula in a spreadsheet (I am not an VBA expert, I'd prefer to use formula instead of VBA coding but if there's no choice, I'd like to know it also).

I need to get the earliest date record based on a specific column (for example column B with different date records) and also based on today's date (because the value needs to be updated monthly).
For example, in the list there are dates ranging the whole year round (which contains records from January to December). When you try to run the formula today, 31 Oct. 2011, it will display the earliest date of October 2011 based on the list. But if ever I'll be running the formula tomorrow, 1 November 2011, the result will be different; it will be the earliest date record of November 2011.

Is it possible to use the Min() function as a reference to the Today() function from a selection?

Thanks for your time,
- bye

 

[Aladin Akyurek]

Hi Excel Gurus,

May I ask for your help, please?
I am currently using MS Excel 2007 and aiming to add a formula in a spreadsheet (I am not an VBA expert, I'd prefer to use formula instead of VBA coding but if there's no choice, I'd like to know it also).

I need to get the earliest date record based on a specific column (for example column B with different date records) and also based on today's date (because the value needs to be updated monthly).
For example, in the list there are dates ranging the whole year round (which contains records from January to December). When you try to run the formula today, 31 Oct. 2011, it will display the earliest date of October 2011 based on the list. But if ever I'll be running the formula tomorrow, 1 November 2011, the result will be different; it will be the earliest date record of November 2011.

Is it possible to use the Min() function as a reference to the Today() function from a selection?

Thanks for your time,
- bye

If you want the earliest date from the B-range falling in the month/year of today's date...

Control+shif+enter, not just enter:

=MIN(IF(B3:B15-DAY(B3:B15)+1=TODAY()-DAY(TODAY())+1,B3:B15))

Adjust the range to suit.

 

[venkat1926]

" earliest date record based on a specific column (for example column B with different date records) and also based on today's date (because the value needs to be updated monthly)"

Perhaps you mean that the earliest of the month specified by today's month



see the sheet with data in column A, today's date in C1 and the formula in F1.

the formula is
=MIN(IF(MONTH(A1:A37)=MONTH(C1),A1:A37))
invoke this formula with control shift enter
you will get the date as number format it as date

Excel Workbook
	A	B	C	D	E	F
1	1/1/2011		10/31/2011			8-Oct-11
2	1/11/2011
3	1/21/2011
4	1/31/2011
5	2/10/2011
6	2/20/2011
7	3/2/2011
8	3/12/2011
9	3/22/2011
10	4/1/2011
11	4/11/2011
12	4/21/2011
13	5/1/2011
14	5/11/2011
15	5/21/2011
16	5/31/2011
17	6/10/2011
18	6/20/2011
19	6/30/2011
20	7/10/2011
21	7/20/2011
22	7/30/2011
23	8/9/2011
24	8/19/2011
25	8/29/2011
26	9/8/2011
27	9/18/2011
28	9/28/2011
29	10/8/2011
30	10/18/2011
31	10/28/2011
32	11/7/2011
33	11/17/2011
34	11/27/2011
35	12/7/2011
36	12/17/2011
37	12/27/2011
Sheet1

 

[bye]

Re: Getting the Earliest Date on a Selection with Today() as Reference - SOLVED

Good morning, Aladin and Venkat,

I really appreciate both of your help.
Venkat, your suggestion works like magic! I've used used your advice.

Thanks so much for both of you,
- bye

 

[bye]

Re: Getting the Earliest Date on a Selection with Today() as Reference - ADDITIONAL

Hi again, Aladin and Venkat,

Kindly excuse me for not mentioning this on my first post.

What if the date range selection contains different years?

Cheers,
- bye

 

[Aladin Akyurek]

Re: Getting the Earliest Date on a Selection with Today() as Reference - ADDITIONAL

Hi again, Aladin and Venkat,

Kindly excuse me for not mentioning this on my first post.

What if the date range selection contains different years?

Cheers,
- bye

See my post...

 

[bye]

Re: Getting the Earliest Date on a Selection with Today() as Reference - SOLVED 2

Hi Aladin,

My apologies for that.
You're solution is correct.

Thanks and also for replying promptly,
- bye

 

[Aladin Akyurek]

Re: Getting the Earliest Date on a Selection with Today() as Reference - SOLVED 2

Hi Aladin,

My apologies for that.
You're solution is correct.

Thanks and also for replying promptly,
- bye

You are welcome. Thanks for providing us feedback.