Excel - Today's Value and Date
Solved
Rocksan
Posted messages
11
Status
Membre
-
Raymond PENTIER Posted messages 58551 Registration date Status Contributeur Last intervention -
Raymond PENTIER Posted messages 58551 Registration date Status Contributeur Last intervention -
Hello,
I would like to submit a small issue about Excel.
I want to display a value corresponding to today's date (somewhere other than in the table where it is located), specifically:
If today is Tuesday, August 14 and my table is as follows:
A B
1 Date Value
2 Monday 13 + 0:14
3 Tuesday 14 + 0:23
I would like + 0:23 to appear in the place of my choosing.
I know how to use the function =TODAY(), but I don’t know how to construct the logical loop that will always display today’s value next to today’s date.
Thank you very much in advance for your help.
Rocksan
I would like to submit a small issue about Excel.
I want to display a value corresponding to today's date (somewhere other than in the table where it is located), specifically:
If today is Tuesday, August 14 and my table is as follows:
A B
1 Date Value
2 Monday 13 + 0:14
3 Tuesday 14 + 0:23
I would like + 0:23 to appear in the place of my choosing.
I know how to use the function =TODAY(), but I don’t know how to construct the logical loop that will always display today’s value next to today’s date.
Thank you very much in advance for your help.
Rocksan
Configuration: Windows XP Internet Explorer 7.0
4 réponses
Hello, Rocksan.
I hope I understood your problem and found a solution, using the VLOOKUP function.
______A_______B______C_____D______E_________F_____G___
1_____________________________08/17/2007__+ 00:17______
2____Date____Value_____________________________________
3___Wed 15___0:14_______________________________________
4___Thu 16___0:23_______________________________________
5___Fri 17___0:17_______________________________________
* 1) You give a name (for example "zone") to the matrix formed by columns A and B.
To do this, select the 2 columns and click Insert/Name/Define and enter "zone".
* 2) In cell E1 enter ""=TODAY()".
* 3) In cell F1 enter ""=VLOOKUP(E1,zone,2)".
Excel will then search vertically, in the 2nd column of the matrix zone formed by columns A and B, for the value contained in the row that starts with the same criterion as E1, that is, today's date. Excel will find the match in A5 and will display the content of B5.
* 4) It's done!
But to get a correct display, select column B and cell F1, then go to:
Cell Format / Number Tab / Custom Category
and, in the Type field, enter the code: "+ hh:mm" (with the space).
I bet a champagne this will suit you!
Best regards, Raymond
I hope I understood your problem and found a solution, using the VLOOKUP function.
______A_______B______C_____D______E_________F_____G___
1_____________________________08/17/2007__+ 00:17______
2____Date____Value_____________________________________
3___Wed 15___0:14_______________________________________
4___Thu 16___0:23_______________________________________
5___Fri 17___0:17_______________________________________
* 1) You give a name (for example "zone") to the matrix formed by columns A and B.
To do this, select the 2 columns and click Insert/Name/Define and enter "zone".
* 2) In cell E1 enter ""=TODAY()".
* 3) In cell F1 enter ""=VLOOKUP(E1,zone,2)".
Excel will then search vertically, in the 2nd column of the matrix zone formed by columns A and B, for the value contained in the row that starts with the same criterion as E1, that is, today's date. Excel will find the match in A5 and will display the content of B5.
* 4) It's done!
But to get a correct display, select column B and cell F1, then go to:
Cell Format / Number Tab / Custom Category
and, in the Type field, enter the code: "+ hh:mm" (with the space).
I bet a champagne this will suit you!
Best regards, Raymond
I would like to try to help you, but I didn't quite understand your problem:
- How do you get "Tuesday 14 + 0:23" in cell B3?
- Is it imperative that the date "Tuesday 14" and the value "+ 0:23" be in the same column B?
- Does "+0:23" indeed represent 23 minutes?
- Is your question really: { Whenever I type "Tuesday 14" in a cell, I want to see the expression "+ 0:23" displayed in it }?
- Could we rephrase it as: { Whenever I type "Tuesday 14" in a cell, I want the value "+ 0:23" to appear in the adjacent cell }?
With all these clarifications, I might have some leads to suggest to you.
- How do you get "Tuesday 14 + 0:23" in cell B3?
- Is it imperative that the date "Tuesday 14" and the value "+ 0:23" be in the same column B?
- Does "+0:23" indeed represent 23 minutes?
- Is your question really: { Whenever I type "Tuesday 14" in a cell, I want to see the expression "+ 0:23" displayed in it }?
- Could we rephrase it as: { Whenever I type "Tuesday 14" in a cell, I want the value "+ 0:23" to appear in the adjacent cell }?
With all these clarifications, I might have some leads to suggest to you.
Hello Raymond.
I had a problem reproducing the layout of the spreadsheet in the forum, sorry (couldn't find the tab key...)
I have a sheet with the days of the week in column A and values corresponding to those days, which are indeed in minutes.
I would like the date of the day to appear at the top of my page (automatically updated using the TODAY() function) and in the adjacent cell, the value corresponding to the date of the day.
I created a sort of electronic time clock and I would like to know where I stand with the "overtime" balance when I open the spreadsheet, without having to look directly at the table.
I hope that's clearer this way. Thank you in advance for your help. Have a great day.
Rocksan
I had a problem reproducing the layout of the spreadsheet in the forum, sorry (couldn't find the tab key...)
I have a sheet with the days of the week in column A and values corresponding to those days, which are indeed in minutes.
I would like the date of the day to appear at the top of my page (automatically updated using the TODAY() function) and in the adjacent cell, the value corresponding to the date of the day.
I created a sort of electronic time clock and I would like to know where I stand with the "overtime" balance when I open the spreadsheet, without having to look directly at the table.
I hope that's clearer this way. Thank you in advance for your help. Have a great day.
Rocksan
Hi
If I understood what you want, in your described case, you just need to put:
in A1 .... =today()
in B1 .... = A1
then, while on B1, go to Cell Format, and choose "Standard". This will give you a number corresponding to today's date (not converted to date format).
Note:
If you put in A1 ... =today(), it changes every time you open your sheet.
If on the other hand, you do ... ctrl ; ... it enters today's date and it won't change until a new manual entry ... ctrl ; ...
--
*** @+ / Furtif ***
*** Eat bananas! ***
If I understood what you want, in your described case, you just need to put:
in A1 .... =today()
in B1 .... = A1
then, while on B1, go to Cell Format, and choose "Standard". This will give you a number corresponding to today's date (not converted to date format).
Note:
If you put in A1 ... =today(), it changes every time you open your sheet.
If on the other hand, you do ... ctrl ; ... it enters today's date and it won't change until a new manual entry ... ctrl ; ...
--
*** @+ / Furtif ***
*** Eat bananas! ***
Hello, Furtif.
Our exchanges with Rocksan have been fruitful, so that's good!
It's a pity she doesn't like bananas, but we'll change her mind; otherwise, how will we, in Guadeloupe, be able to sell our production (even though cyclone Dean ruined everything last Friday)?
I am at your disposal to discuss and exchange on Excel, Word, Access, PowerPoint, and even Outlook or Money.
Bye, Raymond.
Our exchanges with Rocksan have been fruitful, so that's good!
It's a pity she doesn't like bananas, but we'll change her mind; otherwise, how will we, in Guadeloupe, be able to sell our production (even though cyclone Dean ruined everything last Friday)?
I am at your disposal to discuss and exchange on Excel, Word, Access, PowerPoint, and even Outlook or Money.
Bye, Raymond.
Hello, Rocksan
Glad you're happy.
Your approach is the right one regarding the matrix definition.
You will realize the real usefulness of naming cells (or rows, or columns, or ranges of cells also called matrices) in all your applications. It facilitates the copying of formulas and enhances their readability, especially when they are complicated.
Indeed, isn’t it, it "speaks" more to write in H3
=PriceUnit*Qty*(1+VAT)
rather than writing
=B3*D3*(1+$K$1)
if you have previously named "PriceUnit" for column B, "Qty" for column D, and "VAT" for cell K1.
You will also find that there are many cases where the HLOOKUP and VLOOKUP functions are beneficial.
To convince yourself, take a look at the recent discussions in this forum with SAMIJO, Livity, alex, COZIGOU, or even philseul06.
P.S.: There is no tabulation possible in the word processing used in forum messages. You must absolutely use characters (* . _ for example) to space your inputs.
Have a great day.
Raymond
Glad you're happy.
Your approach is the right one regarding the matrix definition.
You will realize the real usefulness of naming cells (or rows, or columns, or ranges of cells also called matrices) in all your applications. It facilitates the copying of formulas and enhances their readability, especially when they are complicated.
Indeed, isn’t it, it "speaks" more to write in H3
=PriceUnit*Qty*(1+VAT)
rather than writing
=B3*D3*(1+$K$1)
if you have previously named "PriceUnit" for column B, "Qty" for column D, and "VAT" for cell K1.
You will also find that there are many cases where the HLOOKUP and VLOOKUP functions are beneficial.
To convince yourself, take a look at the recent discussions in this forum with SAMIJO, Livity, alex, COZIGOU, or even philseul06.
P.S.: There is no tabulation possible in the word processing used in forum messages. You must absolutely use characters (* . _ for example) to space your inputs.
Have a great day.
Raymond
Hi Raymond,
Yes, there is definitely a syntax that is much more eloquent. Thank you for letting me know about these possibilities (I still have so much to discover, by the way...!)
I hope you weren't too shaken by the cyclone... In any case, it seems that you are still connected. I remain amazed by the possibilities offered by the web. A little Swiss girl has a question, and it’s a guy from Guadeloupe who answers her a few hours later. I can't get used to it; it will always belong to the realm of the incredible for me. Probably because these possibilities didn't exist when I was a kid...
Have a good day, and certainly see you soon.
Rocksan
Yes, there is definitely a syntax that is much more eloquent. Thank you for letting me know about these possibilities (I still have so much to discover, by the way...!)
I hope you weren't too shaken by the cyclone... In any case, it seems that you are still connected. I remain amazed by the possibilities offered by the web. A little Swiss girl has a question, and it’s a guy from Guadeloupe who answers her a few hours later. I can't get used to it; it will always belong to the realm of the incredible for me. Probably because these possibilities didn't exist when I was a kid...
Have a good day, and certainly see you soon.
Rocksan
Hello,
I just read the entire post with the questions/answers.
I think I can use =VLOOKUP() for my table.
However, my date is separated for the needs of my table, i.e.:
A1: =YEAR(TODAY())
B1: =MONTH(TODAY())
K1: =My value to retrieve
I'm trying to coincide with your formula but the syntax doesn’t seem to be correct...
I wrote:
=VLOOKUP(YEAR(TODAY())&MONTH(TODAY()),A1:S15,11,)
Excel returns N/A...
Do you have a solution?
Thanks for your help.
I just read the entire post with the questions/answers.
I think I can use =VLOOKUP() for my table.
However, my date is separated for the needs of my table, i.e.:
A1: =YEAR(TODAY())
B1: =MONTH(TODAY())
K1: =My value to retrieve
I'm trying to coincide with your formula but the syntax doesn’t seem to be correct...
I wrote:
=VLOOKUP(YEAR(TODAY())&MONTH(TODAY()),A1:S15,11,)
Excel returns N/A...
Do you have a solution?
Thanks for your help.
The syntax of your formula:
=VLOOKUP(YEAR(TODAY()&MONTH(TODAY());A1:S15;11;)
contains an error; in YEAR(TODAY() there is a missing parenthesis,
it should be YEAR(TODAY())
The logic of your formula also has two errors:
=TODAY() returns a numerical value which is the date; however, your formula contains text, since it is the concatenation of two elements;
furthermore, =TODAY() provides the day, month, and year; yet your two elements only cite
the year and the month!
Finally, if the formula in K1 was meant to be copied down the column, you should have "fixed" the reference range by replacing A1:S15 with $A$1:$S$15.
Therefore, there was no chance that your formula would yield a result ...
=VLOOKUP(YEAR(TODAY()&MONTH(TODAY());A1:S15;11;)
contains an error; in YEAR(TODAY() there is a missing parenthesis,
it should be YEAR(TODAY())
The logic of your formula also has two errors:
=TODAY() returns a numerical value which is the date; however, your formula contains text, since it is the concatenation of two elements;
furthermore, =TODAY() provides the day, month, and year; yet your two elements only cite
the year and the month!
Finally, if the formula in K1 was meant to be copied down the column, you should have "fixed" the reference range by replacing A1:S15 with $A$1:$S$15.
Therefore, there was no chance that your formula would yield a result ...
Salut salut,
I need a little help; I have two files.
The first one, with specific dates (Day, month, year) and corresponding values:
A__________ B___________ C
Date________ y___________ VALUE 1
...__________ ...__________ ....
Date________ y __________VALUE x
And the second one, the main one:
A__________ B__________ C
Start Date ___End Date ____???
The ??? corresponds to a calculation that adds the VALUES from 1 to x.
My problem is that when I fill in the start and end dates in my main file, I need my ??? to show up.
I’m not sure if I should use lengthy formulas or VBE..
I hope I’ve been clear enough and thank you in advance for letting me know if it’s possible or not, and if so, help me out ;)
I need a little help; I have two files.
The first one, with specific dates (Day, month, year) and corresponding values:
A__________ B___________ C
Date________ y___________ VALUE 1
...__________ ...__________ ....
Date________ y __________VALUE x
And the second one, the main one:
A__________ B__________ C
Start Date ___End Date ____???
The ??? corresponds to a calculation that adds the VALUES from 1 to x.
My problem is that when I fill in the start and end dates in my main file, I need my ??? to show up.
I’m not sure if I should use lengthy formulas or VBE..
I hope I’ve been clear enough and thank you in advance for letting me know if it’s possible or not, and if so, help me out ;)
That works! A thousand thanks. I didn't know about the possibility of defining arrays and the VLOOKUP function. I'll make sure to explore the possibilities.
Actually, my two columns A and B are not really adjacent. In my table, they correspond to columns B and K. I couldn't manage to define an array made up solely of columns B and K; it didn’t work, so I had to create an array from B to K and then specify the 10th column in the formula.
That was the right thing to do, wasn't it?
In any case, you provided a solution to my problem and I thank you for that. You deserve champagne!!! I'm raising a virtual toast to your wisdom.
Have a great day. Best regards,
Rocksan