Can excel return specific value based on today's date?

Hello,

I was wondering if this was possible. I have a formula that in order for it to work, it needs to factor in the current day of the quarter for our school year.

For example, today (May 4th) is day 24 of the 4th quarter.

Is there a way through excel that I could have the today function return a specific value to a day of quarter cell?

In my spreadsheet for example I would need to return 22 for 5/2/11, 23 for 5/3/11, etc.  ExcelHelp.xlsx ExcelHelp.xlsx
ExcelHelp.xls
ibewlocal98Asked:
Who is Participating?
 
royhsiaoCommented:
or this using vlookup to retrn the day of the quarter?
Copy-of-ExcelHelp3.xls
0
 
royhsiaoCommented:
Like this?
PIC.JPG
0
 
royhsiaoCommented:
Please see attached example:
You could define a range name and use it in the code.
Will this help?
Copy-of-ExcelHelp-1-.xls
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ibewlocal98Author Commented:
Actually quarter day (Column E) would need to return the value of Day of Quarter (Column B). I was thinking some type of IF function?

For example, if A25=today, then return B25.
0
 
royhsiaoCommented:
0
 
ibewlocal98Author Commented:
the vlookup looks like it might work. although im a little confused as to what =VLOOKUP(TODAY(),A:B, 2, 0) is doing.

If I am correct, vlookup looking for todays date in A, and returning the value in B? What do the 2,0 mean?
0
 
royhsiaoCommented:
Right,
The vlookup looks for today date in first column of the range(A:B). If the date is found the match in the range(A:B), the vlookup return the data in 2nd column.

Here is an example,
http://spreadsheets.about.com/od/excelfunctions/qt/080722_vlookup.htm
0
 
royhsiaoCommented:
in other words,
look up today's date in column A, find exact match and return data from column b.
2 -> 2nd column
0 -> exact match

========
The syntax for the VLOOKUP function is:
= VLOOKUP ( lookup_value , table_array , col_index_num , range_lookup )
lookup _value: The value you want to find in the first column of the table_array.
table_array: This is the table of data that VLOOKUP searches to find the information you are after.
The table_array must contain at least two columns of data. The first column contains the lookup_values.
col_index_num: The number of the column in the table_array that contains the data you want returned.
range_lookup: A logical value (TRUE or FALSE only) that indicates whether you want VLOOKUP to find an exact or an approximate match to the lookup_value. Typing False will return exact matches only.
0
 
ibewlocal98Author Commented:
awesome! thank you!!! perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.