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
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
Please see attached example:
You could define a range name and use it in the code.
Will this help?
Copy-of-ExcelHelp-1-.xls
You could define a range name and use it in the code.
Will this help?
Copy-of-ExcelHelp-1-.xls
ASKER
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.
For example, if A25=today, then return B25.
Like this?
Copy-of-ExcelHelp-2.xls
Copy-of-ExcelHelp-2.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
If I am correct, vlookup looking for todays date in A, and returning the value in B? What do the 2,0 mean?
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
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
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.
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.
ASKER
awesome! thank you!!! perfect.
PIC.JPG