date #4

Hi All
I live in South Africa, and I have an interesting problem that persists
in
Excel 2003 and 2007.
=TEXT(NOW(),"dd mmm yyyy") returns 15 Dec 2006
=month(now()) returns 12
=text(month(now()),"MMMM") returns January
=year(now()) returns 2006
=text(year(now()),"yyyy") returns 1905
Why would this be?
Thanks
Chris

0
12/15/2006 6:06:39 PM
excel 39879 articles. 2 followers. Follow

2 Replies
357 Views

Similar Articles

[PageSpeed] 55

Chris,

=text(month(now()),"MMMM")

should be

=text(now(),"MMMM")

and

 =text(year(now()),"yyyy")

should be

 =text(now(),"yyyy")
or just
 =year(now())

Otherwise, you are calcing the month of the 12th day after 12/31/1899, or the year of the 2,006th 
day after 12/31/1899.  That is just how dates work in Excel.

HTH,
Bernie
MS Excel MVP


"chris lane" <chris.1.lane@gmail.com> wrote in message 
news:1166205999.331514.320870@n67g2000cwd.googlegroups.com...
> Hi All
> I live in South Africa, and I have an interesting problem that persists
> in
> Excel 2003 and 2007.
> =TEXT(NOW(),"dd mmm yyyy") returns 15 Dec 2006
> =month(now()) returns 12
> =text(month(now()),"MMMM") returns January
> =year(now()) returns 2006
> =text(year(now()),"yyyy") returns 1905
> Why would this be?
> Thanks
> Chris
> 


0
Bernie
12/15/2006 6:15:41 PM

Thanks very much, Bernie


Bernie Deitrick wrote:
> Chris,
>
> =text(month(now()),"MMMM")
>
> should be
>
> =text(now(),"MMMM")
>
> and
>
>  =text(year(now()),"yyyy")
>
> should be
>
>  =text(now(),"yyyy")
> or just
>  =year(now())
>
> Otherwise, you are calcing the month of the 12th day after 12/31/1899, or the year of the 2,006th
> day after 12/31/1899.  That is just how dates work in Excel.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "chris lane" <chris.1.lane@gmail.com> wrote in message
> news:1166205999.331514.320870@n67g2000cwd.googlegroups.com...
> > Hi All
> > I live in South Africa, and I have an interesting problem that persists
> > in
> > Excel 2003 and 2007.
> > =TEXT(NOW(),"dd mmm yyyy") returns 15 Dec 2006
> > =month(now()) returns 12
> > =text(month(now()),"MMMM") returns January
> > =year(now()) returns 2006
> > =text(year(now()),"yyyy") returns 1905
> > Why would this be?
> > Thanks
> > Chris
> >

0
12/15/2006 6:51:33 PM
Reply:

Similar Artilces:

date stamp #2
I am a new user to excel and would like to know how I put a stamp in the corner of my workbook that says the file that it is under. Ex h:\xw\... Also would this change if I change the file it is in. Thankyou Jessica ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: =LEFT(CELL("filename",A1), FIND("]", CELL("filename",A1))) Note that the file must be saved for this to work. In article <jdorion.vgeay@excelforum-nospam.co...

Date #6
hi I have a workbook that contains several formatted cells cell "A1" is formated as dd/mm/yyyy and has the today() formula in it what i want to do is to copy the value of "A1" and paste it into "A4" ie. "A1" = 13/01/2005 i only want the text value to be copied and pasted into A4 "A4" = 13/01/2005 When i try this i get the numeric value of 38365 which is excels text value of the date. I need the above format thanks in advance kevin Pre format or use edit>paste special or use a formula =text(a1,"yourformathere") or...

Add a date to a Userform
I am using Office 2010 in advance of roll-out in our organization. I have been testing some of our VBA enabled templates for compatibility and have run into a problem. Specifically a lose of the Calendar control. Since we have a number of Userforms that use the calendar control, I need to come up with some sort of replacement. Any suggestions? I thought there was a date picker control, but I can not find it (I have installed the complete Office 2010 Professional Plus) The calendar control uses the library c:\program Files\Microsoft Office\Office13\MSAL.OCX Usually th...

Date Range on Report in Money 2003
I have a favorite report in Money 2003 which lists scheduled payments within a range of dates. My file was upgraded from Money 2000. I used to have the problem of the report omitting many of the scheduled transactions within the date range. That was fixed by running the Salvage program. Now the problem is the other way around: the report shows transactions BEYOND the date range, as well as those within. Is there a fix for that? I still have the Salvage program. :-) Thanks, Elliott ...

Insert date into file import
I have a database that imports a .csv file generated by anotherprogram. Unfortunately this program doesn't include a date in eachrecord in the .csv file.I can import the .csv file into my table alright, but I need to attach/insert a date (which I can get via VB code) into each importedrecord. Any ideas?At the moment I'm using the Docmd.TransferText command. have you tried importing the data, then running an Update query on thetable, to add a date to each record?hth<b_lwalker@hotmail.com> wrote in messagenews:1174264665.772034.212550@n59g2000hsh.googlegroups.com...> I have a ...

Named Printers #4
I want to use Named Printers for PA Purchase Order Entry. However, when I choose Project from the Task Series drop-down, nothing is showing up. What could be the problem? -Hari ------=_NextPart_0001_6AC20EE0 Content-Type: text/plain Content-Transfer-Encoding: 7bit In this list, "Project" doesn't mean Project Accounting, that is just the series. Any application/module can put themselves in this list in any location- however it would seem the perfect location for Project Accounting report. Looking at my list, this also is empty for me as well. Briefly looking a PA sourc...

pfbackup.exe #4
Hello, I installed Office 2000 and Outlook 2003 on my computer. With my outlook2000 version, I used to use a small utily of microsoft that allow me to backup my pst files (pfbackup.exe). Now i'm trying it but it's not working. I realise that when a user had Outlook2000 with the pfbackup.exe installed and correctly working, when he makes an update to the 2003 version, it's still working. But when we erase everything and make a new installation of directly Outlook2003 and the fpbackup.exe, it don't work. Thanks for your help Jean-Luc <jlamou2@yahoo.fr> wrote: > I in...

date format #32
i have a sheet where in some cells the date are in ddmmyy format along with text & in some cells mmddyy format along with text. is there any command to change all in mmddyy format On Sat, 20 Aug 2005 03:40:03 -0700, "Ankur" <Ankur@discussions.microsoft.com> wrote: >i have a sheet where in some cells the date are in ddmmyy format along with >text & in some cells mmddyy format along with text. > >is there any command to change all in mmddyy format You need to describe more precisely exactly what is in the cells, and how one can tell the difference betwee...

Excel default date format
1) The default date format for Excel is "21-Mar" when I enter date recognized data. This is unusable for me, I need the year displayed. How do I change the default date format to display the year. 2) With default also, Excel sometimes requires "=" in equations and sometimes not; i.e., if I enter a '-' it converts the cell to an equation. How do i stop this so that it only makes equations when I enter '='. 3) How do I get help on Excel inconsistancies like the above in Excel documentation anywhere? mjk #1. If I type in the date that includes the ye...

how do i set an auto date function in excel or word?
I would like all documnts in both word and excel to automatically date as the document or report is prepared. =NOW() "hpkcommish" wrote: > I would like all documnts in both word and excel to automatically date as the > document or report is prepared. for excell Go to View> Header & footer >custom header footer then click on the clock pic & the date pic (choose 1st if you want it in the left middle or right feild) For word basically the same Cheers peterm "hpkcommish" wrote: > I would like all documnts in both word and excel to automatica...

Problem with copying a to from date to another cell
I am trying to enter the first 7 days of the year in one cell. ie: A1 -> 1/2/05 to 1/8/05 And then I want to drag that down to 53 cells and have it copy in the following format. ie: A2 -> 1/9/05 to 1/15/05 A3 -> 1/16/05 to 1/22/05 etc... Is this possible? I would do it by using two separate columns, typing in the two top row dates, the highlight the area you wish to fill, then Edit > Fill > Series > Trend..........then when you have the two columns of dates, use a third helper column with this formula copied down...... =TEXT(A1,"mm/dd/yy")&...

Text to Columns #4
I have a list of the Fortune 1000 companies in one cell. Is there a way to convert this information into columns with: Company City State Telephone 1 Wal-Mart Stores, Inc. Bentonville, AR 479-273-4000 2 Exxon Mobil Corporation Irving, TX 972-444-1000 3 General Motors Corporation Detroit, MI 313-556-5000 4 Ford Motor Company Dearborn, MI 800-392-3673 5 General Electric Company Fairfield, CT 203-373-2211 6 ChevronTexaco Corporation San Ramon, CA 925-842-1000 7 ConocoPhillips Houston, TX 281-293-1000 8 Citigroup Inc New York, NY 212-559-1000 9 International Business Machines Corporatio...

test #4
test... Posted at http://www.exemell.com/ ...

Money 04 #4
My Win XP COMPLETLY CRASHED with a nasty virus. I did not how to recover from it. I bought a new hard drive and reinstalled XP, starting from scratch. I have my old hard drive set up as a slave and can access files from it. I cannot open Money from the old drive, XP is fried. How can I get my Money file from this old drive, set up as a slave, into my new drive and Money file. Where do I find it on the old drive? In microsoft.public.money, Gary wrote: >My Win XP COMPLETLY CRASHED with a nasty virus. I did not how to recover from >it. I bought a new hard drive and reinstalled XP, s...

Word 2007 Show Markup checkmarks and Date/Time Issue
Hi, I have a new HP desktop computer and finally got Word 2007. I have two questions. In the Review section of Word 2007, the Show Markup subsection has checkmarks for ink, comments, etc. as the default selection. Is there any way to change this default and uncheckmark them so it isn't automatic? I have looked everywhere online for a solution and can't find one. Second, in the bottom right-hand corner of my monitor, the time is showing as usual. However, when I first got my computer (last week), it also showed the date (I loved this feature). After re-starting my compute...

Date Format Problems
Is there anyway to make excel change the numbers I type in to the appropriate date? If I type 112603, how can I get the cell to display 11/26/03. I have tried formatting the cell, but it just doesn't seem to come out right. Any help would be greatly appreciated. Thank you Jennifer Only through VBA. See Chip Pearson's website. http://www.cpearson.com/excel/DateTimeEntry.htm Gord Dibben XL2002 On Fri, 5 Dec 2003 08:06:01 -0800, "Jennifer W" <icecream3@earthlink.net> wrote: >Is there anyway to make excel change the numbers I type in to the appropriate date?...

Resource Scheduling #4
We currently use Outlook 2002 and exchange server 5.5. We have a group of users who are tutors. The tutor's appointments are scheduled by a scheduling team. We understand how to allow the scheduling team the correct permissions to make entries in the tutors' diaries using delegate permissions, however, we want to create a situation where the tutors can make entries in their own diaries but cannot change entries made by the scheduling team. We carried out the following on a test tutor diary in Outlook. Tools Options; Calendar Options; Resource Scheduling, we ticked the option...

Problem formatting date values in chart...
I have a blood pressure tracking spreadsheet -- at a minimum, it tracks Systolic and Diastolic pressure for a given day. A B C E.g.: Date Systolic Diastolic 1 9/3/04 120 90 2 9/4/04 123 87 3 9/5/04 129 79 I currently have a simple line graph that shows systolic data points over diastolic data points. The left hand (Y axis) value is "Pressure". My problem is that I want the "date" values of Col. A to display at the bottom of the graph as a label with each corresponding data point and...

Due date VS processing date in Money '06
This is really bothering me. In my previous version, when I entered a due date for an epay, my register showed that date. It didn't remind me that I need to have XXX dollars in my account on due date - 4 and then change the date in my register to due date - 4. I want my register to show the due date I entered. I have enough sense to know I need money in my account. How can I change this? The check box about using processing date instead of entered date is NOT checked. Did my upgrade magically switch me to a bill payment service that is actually drafting my account early and pu...

Event ID 8331 #4
Point possibly who why it still no official statment or Fix from MS gives. All workarounds are published, do not help ! ...

Calendar To Stay at Last Date Entered
I have a calendar in a form. We do data entry for information that could have occurred today, yesterday, or a couple of days ago, like over a weekend. The form always goes back to todays date. How do I get the calendar to stay at the last date entered? Somehow you will need to record the last date entered in a table somewhere. I use a system table for such items. Bill wrote: >I have a calendar in a form. We do data entry for information that could >have occurred today, yesterday, or a couple of days ago, like over a weekend. > The form always goes back to todays date. How ...

format cells #4
hi. how can i set by default the cell formatting to 'general' or 'number' or even 'text'? i mean i dont want excel to correct let's say: 1.5 to 01.may or 9/12 to 09.dec. is this possible? thanks. (i have some programs which export data to excel and instead of numbers in excel appear dates and i can't set the cell format before the data is exported) -- notrace2004 ------------------------------------------------------------------------ notrace2004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34817 View this thread: http://www....

How conditional format line with date of today #2
Hi, tried setting conditional where cell = today(). However, today was converted to ="today()" and the format was no applied. The format does work if I hardcode the date. I.e. it work if I pu =04/11/04 instead of =today(). However I need to use the today() function. Any other suggestions? Thanks -- dewsbur ----------------------------------------------------------------------- dewsbury's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1608 View this thread: http://www.excelforum.com/showthread.php?threadid=27535 Select the cell to CF. Format>...

Offline with 4.0 and McAfee Antivirus
We're running a pretty standard 4.0 implementaion alongside McAfee VirusScan Enterprise 8.5.0i. When trying to go offline, the synch process breaks and the AV software alerts and deleted a file called {3FD2B963-1730-DD11-98CF-0019B9EB88DE}.bcp it thinks it's a virus. Anybody else seeing the same thing or have any ideas on possible solutions? isn't it possible to exclude the affected folder from scanning? "Matt" <mgd@kynetix.com> schrieb im Newsbeitrag news:%23$a%23$2KxIHA.5124@TK2MSFTNGP04.phx.gbl... > We're running a pretty standard 4.0 implementaio...

4.0 enterprise edition license
Hi all, We plan to install ms crm 4.0 enterprise edition for one of our customer. 5 organizatons will be created and 20 users will be users of each organization. My question is: A new license is necessary for each user for every organization? I mean, do I need to purchase 100 licenses (20 users X 5 organizations) or 20 license is enough? Hi, if you use 20 different users, you need 100 licences mit freundlichen Gr��en Michael Sulz ieQ-network AG eMail: michael.sulz@ieq-network.de Thank you for the reply. In the test environment, I have created 5 organizations and 3 users. These 3 us...