todays date

Hi I have a cell that I want to know when something will be finished i.e.
today()+30.which gives 02/07/04. This is ok until I open the spreadsheet
tomorrow when I will get 03/07/04. How can I get the date 02/07/04 to remain
every day or do I just enter the date manually?
Thanks tricia


0
6/2/2004 1:10:46 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
1018 Views

Similar Articles

[PageSpeed] 16

Hi Tricia!

You've noted that TODAY() is volatile and will change on recalculation 
if the date has changed.

You might prefer entry using:

Ctrl + ;

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au 


0
njharker (1646)
6/2/2004 2:06:48 PM
Hi Tricia,
You would need a macro.

If you say you have a cell,  you are going to get a specific
solution that only works for one cell on the active worksheet.

Don't you have to enter something manually anyway?

You can use  Ctrl+; (semicolon) to get a constant for the
current date and use a formula in A2 for  today+30
   =A1+30

You can use an Event Macro to check if a cell on the same
row as something you update is empty and if it is to
fill today's date + 30.

For a quick example   suppose you want
Today's date as a constant in  Column A,  date + 30
in column B  whenever a new entry is made in Column C.
The actual way new entry would be determined (as opposed
to an update to cell in column C) would be if  Column A in the row is still empty.

the following is modified from   #autodate in
  Event Macros, Worksheet Events and Workbook Events
  http://www.mvps.org/dmcritchie/excel/event.htm#autodate
If this is the type of thing you want to do,  please look over the
entire page.

Private Sub Worksheet_Change(ByVal Target As Range)
'to install -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'.   Paste this Worksheet
' event macro into the module.
  If Target.Column <> 3 Then Exit Sub
  If Target.Row = 1 Then Exit Sub
  If IsEmpty(Target.Offset(0, -2)) _
         and IsEmpty(target.offset(0,-1)) Then
    Target.Offset(0, -2) = Date
    Target.offset(0, -1) = Date + 30
  End If
End Sub

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Tricia" <tricialal2000@yahoo.co.uk> wrote in message news:uX30GMKSEHA.2408@TK2MSFTNGP10.phx.gbl...
> Hi I have a cell that I want to know when something will be finished i.e.
> today()+30.which gives 02/07/04. This is ok until I open the spreadsheet
> tomorrow when I will get 03/07/04. How can I get the date 02/07/04 to remain
> every day or do I just enter the date manually?
> Thanks tricia
>
>


0
dmcritchie (2586)
6/2/2004 2:08:24 PM
If you're using the TODAY or NOW function then they will always recompute. 
An easy way to enter today's date as a constant is to press Ctrl+; then 
another cell can reference that cell, e.g., =A1+30

-- 
Jim Rech
Excel MVP
"Tricia" <tricialal2000@yahoo.co.uk> wrote in message 
news:uX30GMKSEHA.2408@TK2MSFTNGP10.phx.gbl...
| Hi I have a cell that I want to know when something will be finished i.e.
| today()+30.which gives 02/07/04. This is ok until I open the spreadsheet
| tomorrow when I will get 03/07/04. How can I get the date 02/07/04 to 
remain
| every day or do I just enter the date manually?
| Thanks tricia
|
| 


0
jrrech (1933)
6/2/2004 2:09:25 PM
"Tricia" <tricialal2000@yahoo.co.uk> wrote in message
news:uX30GMKSEHA.2408@TK2MSFTNGP10.phx.gbl...
> Hi I have a cell that I want to know when something will be finished i.e.
> today()+30.which gives 02/07/04. This is ok until I open the spreadsheet
> tomorrow when I will get 03/07/04. How can I get the date 02/07/04 to
remain
> every day or do I just enter the date manually?
> Thanks tricia
>
Hi the ctrl+; is what I need. Nothing too complicated. I have different
quantities of things i.e toys =30, books = 40 and if 2 or 3 were used each
day starting from today when would they be finished. Too much time on my
hands. Thanks Tricia


0
6/2/2004 2:33:48 PM
Reply:

Similar Artilces:

how come =date(year(today()),month(today())+6,today()) show 2097?
While evaluating, everything goes fine till it reaches today(), as 39216 and suddenly the result is the one given above. Why? Sorry, got the error. "dindigul" <padhye.m@gmail.com> wrote in message news:esGfEAalHHA.492@TK2MSFTNGP04.phx.gbl... > While evaluating, everything goes fine till it reaches today(), as 39216 > and suddenly the result is the one given above. Why? > Maybe you meant: =date(year(today()),month(today())+6,day(today())) I wasn't sure if you found a solution or not. dindigul wrote: > > Sorry, got the error. > "dindigul&...

Money date formats
How do you apply a change to the date format for MS Money Have you tried setting the date format on the regions control panel? Many Money formats come from there. "michael furniss" <anonymous@discussions.microsoft.com> wrote in message news:055601c39837$711bb3d0$a401280a@phx.gbl... > How do you apply a change to the date format for MS Money ...

View Only Today's Tasks
OL 2003, XP Home When viewing the calendar and the task pad at the same time, I want to see today's tasks and past due tasks, but when I go to View > Taks Pad View > Today's Tasks I still get tasks for all dates (but no tasks without due dates). How can I fix the filters that it seems I messed up? -- ICQ# I also can't get it to display tasks for Active Tasks for Selected Days. It keeps showing the same tasks as Today's Tasks! Filter Hell ahhhhh! -- ICQ# "Diggy" <jamesOU812@hotmail.com> wrote in message news:%23pLxtWasDHA.2464@TK2MSFTNGP12.phx.gbl...

Making my own data file (pst) the outlook today file
I have just setup Outlook 2003. I have opened my old PST file. I wish to use this and make this my default data file. I will therefore not require the Personal Folder which is the Outlook today default I think I need to make my opened PST file the default file before I can delete the Personal Folders file But how do I do this...... Thanks Go to Tools | E-mail Accounts, select View or change existing e-mail accounts, click Next, and then choose your preferred file in the "Deliver new e-mail to the following location" dropdown box. Click Finish, and then you will be able to close...

Date Format Help
Can someone tell me where to go to set my explorer date format to YYYY/MM/DD? Thanks Richard Richard wrote: > Can someone tell me where to go to > set my explorer date format to YYYY/MM/DD? Now is a great time to point you to one of the easiest ways to find information on problems you may be having and solutions others have found: Search using Google! http://www.google.com/ (How-to: http://www.google.com/intl/en/help/basics.html ) How to change date formatting in Windows XP http://www.google.com/search?q=How+to+change+date+formatting+in+Windows+XP First l...

Charting dates on the Y axis without weekends
I need to graph employees' performance in turning in reports on time. So the y axis is the date and the x axis is the employees' names. Is it possible to eliminate weekends on the y axis? Otherwise, employees who turn in a report Monday that was due Friday look like they are 3 days late, when actually they are just one day late. -- JenTK2006 In article <9418C405-F3DE-43C2-A236-3CE9D8FE1942@microsoft.com>, JenTK2006 @discussions.microsoft.com says... > I need to graph employees' performance in turning in reports on time. So the > y axis is the date and the x a...

"gain since opening date"
Hi. M06: Investing, Investing Accounts, "Gain Since Opening Date" Could someone explain to me what this means? For one investment the 'current value' is 385.34 and the 'value on 4/5/07' is 381.14 and 'gain since opening date' is 594.15. The $594.15 seems to not mean anything relevant to me because it's either wrong or I don't know what it mean. Where is this number coming from? This is an investment that I reinvest the quarterly dividend but other than that, there have been no transactions in the account. ??? In microsoft.public.money, theo...

Date Format Problem #3
Hello, I've imported a set of data from an external source into Excel. One of the fields is the date and it is displayed in Excel as:- 2003-03-25 11:34:04. When I try to change this however to 'dd-mmm-yy' format there is no change. It also means that calculations based on this date can't be made. If I double click the cell with the '2003-03-25 11:34:04' date (as if I was to edit the contents) and immediately hit enter (thus not making any changes), then the format does change to the 'dd-mmm-yy' format and calculations can be made. Is there any way ...

Outlook Today
I have two problems in Outlook Today. Here's the scenario. Today is Saturday 3rd Jan, 2004. In Customize Outlook Today I have "Show this number of days in my calendar as 7 " In Outlook Today under the Calendar column I have a multi-day event correct displayed under today. However, even though this multi-day event ends on Tuesday 6th Jan I cannot see any further listings for it in the Calendar Column. Should I not see it listed for Sunday and Monday and Tuesday as well? The second problem is I have an appointment penned in for Sunday 4th, 2004. (i.e. tomorrow) yet this is now...

Date in cell
If I enter 5/13/04 in a cell, how do I get the spreadsheet to format the cell to read: Thursday, May 13 Example 2: 5/23/04 = Sunday, May 23 Thanks in advance, -- ~Jeff~ [Microsoft Windows XP Pro,Office 2000] Hi Try setting it to Custom and putting dddd, mmmm dd in the box. -- Andy. "~Jeff~" <~Jeff~@~nomail~.com> wrote in message news:urWb0$OOEHA.3752@TK2MSFTNGP12.phx.gbl... > If I enter 5/13/04 in a cell, how do I get the spreadsheet to format the > cell to read: > > Thursday, May 13 > > Example 2: > > 5/23/04 = > > Sunday, May 23 &...

How do I save my Customized Outlook Today when it will not save b.
I am trying to set my Outlook Today to the Winter settings. I will go into Customize Outlook Today, make the changes I want, then I will click "Save Changes." What I want it displayed, untill I go to...say my inbox. When I go be to Outlook Today, it displayes the default settings again. How can I fix this? ...

today
Hi I was wondering if there's a way to add today's date statically (so it won't auto update) to a cell inside a formula. I would like the cell to enter today's date automatically when a value is placed in cell A:12, something like this; =IF(NOT(A12=0),TODAY()," ") This works except this will update the date each time I open the workbook and I don't want that. Thanks. You could change the formula to something like: =IF(A12=0,"",DATE(2007,11,15)) for today's date. I've made a few other changes - got rid of NOT and reversed the order, a...

the dates on cell format make different dates.
Please, I'm just getting farther from the answer and I have to be up for church soon. First question. I'm obtuse but when I type in a date, eg jan 1 05 and then format/cell/date I select a date format and it morphs in to a completely different date. 2nd question: to change case, use UPPER or LOWER function. But, how does that work to use the cell itself or a group of cells. If anyone can help, I'll owe you forever. carrie, washington state carrieaa@hotmail.com 1) What does the date morph into? 2) Take a look at David McRitchie's macros: http://www.mvps.org...

Extra field in SAles Date Inquiry ZOom
Our company distributes products to customers. We fill orders in our warehouse and then send them to Customers. W track date fulfilled, actual Ship date but we would like to complete the process by tracking Delivered date (from our courier) Therefore we would like a field in the SAles Date Inquiry Zoom called Delivery DAte added so we can populate it and report on it through Smart list with the other dates. I guess that field has to really be somewhere else in trx mode so it shows up in the Inquiry field . thanks ---------------- This post is a suggestion for Microsoft, and Micros...

Date calculation question
I have a submission date (reg date field) & an expected due date (using Workday to calculate 3 workdays from submission date, minus holidays). I would like to change the due date so if the document is submitted after 3:00pm, an extra day is added. How would I do this? Thanks, JoAnn If you include the time in the submission date, one way: A1: 27 February 2008 3:30 pm B1: =WORKDAY(A1,3+(MOD(A1,1)>15/24), holidays) assuming no holidays, this will return 4 March 2008 another, simpler, way: B1: =WORKDAY(A1+8.99999/24,3) In article <C26E5809-869A-4732-A977-CD...

Find (Today-21) in a range of dates
I need to write a formula which will search a range of cells containing dates and if any of those is greater than todays date less 21 days, then return the text "not due". This is for a customer data base which has home page showing all customers, and subsequent pages for indvidual customers. As contact dates are added for each customer on their own sheets, then the home page should show if they are due (or overdue) for a call =IF(MAX(rng)>(TODAY()-21),"not due","due") -- HTH RP (remove nothere from the email address if mailing direct) "JG&quo...

Charting Dow vs. Portfolio with dates
Given three columns - the date, say 30 to 90 days, the Dow Average over the same time, and a portfolio value, how can they appear in a sensible fashion? The Dows is about 10,000 and the portfolio is over 1,000,000, and the dates are 7/1/04 etc. I get two very distinct lines for the first two and gibberish for the dates. How to Handle? Please comment. If you have a heading in the date column, delete it Select a cell in the table, and click the Chart Wizard button Create a line chart from the data Right-click on the portfolio line Choose Format Selected Series On the Axis tab, select Secon...

Outlook Today
I have duplicate "Outlook Today - [Personal Folders]" on my folder list. They seem to be exact duplications. When they are expanded they have the same subfolders and changes to one are reflected in the other. Is there some way to get rid of the duplicate folder? Also, below the second "Outlook Today - [Personal Folders]" is an additional folder called "Personal Folders" in the same vertical hierarchy as the two "Outlook Today - [Personal Folders]". When I click on the "Personal Folders" folder I get a message that says "! Unable ...

text date to date format
I have column filled with: 'Apr 3 2005 4:37PM And I need to change it to: 04/03/05 4:37PM How do I get a textual date column into a sortable date/time column? I'm having problems because it sorts everything by alpha then numeric. For example, I have "Apr 15, 2005" before "Aug 10, 2003". I need to get these sorted by year, month, day...not alphabetically. Thanks in advance for your help! you are right: the filter is not a time but a alpha sort you may change the cell format or copy the value in an other cell and put the new format in the new column. ...

Julian dates #2
How do you select Julian dates and not regular month and days? Hi have a look at http://www.cpearson.com/excel/jdates.htm -- Regards Frank Kabel Frankfurt, Germany Talltrees wrote: > How do you select Julian dates and not regular month and days? Julian dates are dates that start on January 1, 4713 BCE at noon in Greenwich, so today's date is 2453071. To get Julian dates, just add 2415018.5 for the 1900 date system or 2416480.5 for the 1904 date system (adjust for your time zone). You can find out more about Julian dates here: http://aa.usno.navy.mil/data/docs/JulianDate....

Auto filling semi-monthly dates
How do I autofill semi-monthly dates into a column. (ie Jan 1 2000 thru Dec 2008) Thanks -- Lawdog If you want 1-Jan, 15-Jan,1-Feb, 15-Feb,... then enter this in A1 =1/1/2008 then this in A2 =IF(DAY(A1)=1,A1+14,DATE(YEAR(A1),MONTH(A1)+1,1)) and copy down till you get 31-Dec "Lawdog" wrote: > How do I autofill semi-monthly dates into a column. (ie Jan 1 2000 thru Dec > 2008) > > Thanks > -- > Lawdog Thanks -- Lawdog "Sheeloo" wrote: > If you want 1-Jan, 15-Jan,1-Feb, 15-Feb,... > then enter this in A1 > =1/1/2008 > then this in A2...

Task View to Display All Task between Today-30 and Today+30
Is it possible to create a View in Task folder such that all tasks due between Today-30 and Today+30 are displayed? In the Advanced Filter setting, I have tried the following setting in vain: Field: Due Date Condition: between Value: date()-30 and date()+30 Thanks for your very kind help in advance. Regards, Armstrong ...

Hot Date
I have a column of dates (assume A1:A45) and I would like to highlight the first date in the column (reading top to bottom) that is equal to, or nearest to a specified date (in Cell B1), but NOT less than the specified date. The dates will all be from the same month and will be (should be!) in chronological order, reading top to bottom. The same date may appear more than once in the column. Some dates will not appear at all. For example: A1 02/04/2008 A2 03/04/2008 A3 05/04/2008 A4 05/04/2008 A5 10/04/2008 A6 13/04/2008 A7 13/04/2008 A8 17/04/2...

the date always friday
Hi Guys, Anyone can help me? Example, today is 28/11/2005. I would like make a template with formula on A1. On the A1 always show the Friday when this template is open. Example the template open on 28/11/2005, therefore on A1 will be show 2/12/2005. If the template open on 2/12/2005, it is still show 2/12/2005, but when the worksheet open on 3/12/2005, it will show the 9/12/2005. Anyone can help me please? Many thanks for your help guys. Best regards Richard Kennedy Many Try this: A1: =TODAY()+CHOOSE(WEEKDAY(TODAY(),2),4,3,2,1,0,6,5) Is that what you're looking for? **********...

today()
I wish to have a formula in a3 such as: "Concatenate(a1,a2)" where a1 contains the text "today is" and a2 contain the function today(). Unfortunately, concatenate gives me: "today is37878", 37878 being the serial number of the date. I have tried formatting each cell every which way to get the date as it is shown in cell a2, but to no avail. There also seems to be no function to return the serial number back to a readable date. any suggestions? Thanks Bern, A couple ways to do that. ="Today is " & TEXT(TODAY(),"mm/dd/yy") This re...