?
Solved

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

Posted on 2011-05-04
9
Medium Priority
?
391 Views
Last Modified: 2012-08-13
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
Comment
Question by:ibewlocal98
  • 6
  • 3
9 Comments
 
LVL 6

Expert Comment

by:royhsiao
ID: 35690419
Like this?
PIC.JPG
0
 
LVL 6

Expert Comment

by:royhsiao
ID: 35690490
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
 

Author Comment

by:ibewlocal98
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Expert Comment

by:royhsiao
ID: 35690825
0
 
LVL 6

Accepted Solution

by:
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

by:ibewlocal98
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

by:royhsiao
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,
http://spreadsheets.about.com/od/excelfunctions/qt/080722_vlookup.htm
0
 
LVL 6

Expert Comment

by:royhsiao
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

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question