Solved

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

Posted on 2011-05-04
Medium Priority
391 Views
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
0
Question by:ibewlocal98
• 6
• 3

LVL 6

Expert Comment

ID: 35690419
Like this?
PIC.JPG
0

LVL 6

Expert Comment

ID: 35690490
You could define a range name and use it in the code.
Will this help?
Copy-of-ExcelHelp-1-.xls
0

Author Comment

ID: 35690548
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

LVL 6

Expert Comment

ID: 35690825
0

LVL 6

Accepted Solution

royhsiao earned 2000 total points
ID: 35690927
or this using vlookup to retrn the day of the quarter?
Copy-of-ExcelHelp3.xls
0

Author Comment

ID: 35691352
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

LVL 6

Expert Comment

ID: 35691631
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,
0

LVL 6

Expert Comment

ID: 35691672
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

Author Comment

ID: 35691704
awesome! thank you!!! perfect.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.