Set date field year to 2005 - Worksheet function?

Hi there,

I have a spreadsheet of dates, EG: '10/09/1983', '03/08/1985'. I need
to change these all to be in the year 2005, so '10/09/1983' becomes
'10/09/2005'. I assumed it would be a case of just doing a custom
format DD MMMM 2005 but this doesn't work. Is there an easy worksheet
function to do this or will I need to write a macro?

Cheers,

Chris

0
10/31/2005 6:45:31 PM
excel 39879 articles. 2 followers. Follow

2 Replies
398 Views

Similar Articles

[PageSpeed] 25

If it's a one timer you can use formulas

=DATE(2005,MONTH(A1),Day(A1))

will take the date in A1 and change the year to 2005
then you can just copy and paste special as values over the old dates,
finally delete the help formulas


-- 

Regards,

Peo Sjoblom


"Chris Ashley" <chris.ashley2@gmail.com> wrote in message
news:1130784331.091854.273600@g49g2000cwa.googlegroups.com...
> Hi there,
>
> I have a spreadsheet of dates, EG: '10/09/1983', '03/08/1985'. I need
> to change these all to be in the year 2005, so '10/09/1983' becomes
> '10/09/2005'. I assumed it would be a case of just doing a custom
> format DD MMMM 2005 but this doesn't work. Is there an easy worksheet
> function to do this or will I need to write a macro?
>
> Cheers,
>
> Chris
>


0
terre081 (3244)
10/31/2005 6:50:58 PM
try this after selecting the dates to change

Sub chgdate()
For Each c In Selection
c.Value = DateSerial(2005, Month(c), Day(c))
Next
End Sub

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Chris Ashley" <chris.ashley2@gmail.com> wrote in message
news:1130784331.091854.273600@g49g2000cwa.googlegroups.com...
> Hi there,
>
> I have a spreadsheet of dates, EG: '10/09/1983', '03/08/1985'. I need
> to change these all to be in the year 2005, so '10/09/1983' becomes
> '10/09/2005'. I assumed it would be a case of just doing a custom
> format DD MMMM 2005 but this doesn't work. Is there an easy worksheet
> function to do this or will I need to write a macro?
>
> Cheers,
>
> Chris
>


0
Don
10/31/2005 7:25:31 PM
Reply:

Similar Artilces:

drop down arrows only show up when in the field
Is there any way to format a drop down list on a form so that the drop down arrow appears all the time, and not just when you tab or click into that field? I'm trying to give my users an idea of which fields are drop down versus free text. Thanks for your help. No, not if you're using a protected form and dropdown formfields. If you change the template to display a userform (http://www.word.mvps.org/FAQs/Userforms/CreateAUserForm.htm) and just insert the resulting values into the document, the dropdowns in the userform do always show the arrow by default (althoug...

LookUp Function #3
Hello! I'm running Excel XP and trying to figure how to run vlookup from tw colums. The form is for working out the next standard size up. Enter number i A and a number in B and in C it pulls thro the code from 'datarange where A is more then or Equals 'datarange coloum A' AND B is more the or Equals 'datarange coloum B' Any Ideas. Please find attached JPG. Hope i've explained enough. Thanks Ala +---------------------------------------------------------------- | Attachment filename: excel1.jpg |Download attachment: ...

Money 2005
Hello, I use Microsoft Money 2005 now for a while, and everytime I exit the app, it connects and uploads the changes to MSN Money. Is there a way to disable this setting? I would like to delete all of my financial data on MSN Money and not have MS Money upload it there again. If there is a way to stop it, please let me know. Thanks, Brett No problem: Tools: Settings: Access your Money data on the Web. Then you'll be able to disable it. -- "Brett" <baisley@h o t ma i .com> wrote in message news:O9vVj9ZqFHA.1336@TK2MSFTNGP11.phx.gbl... > Hello, > >...

Character limit in bcc field?
Hi all, we're currently trying to email all of the contacts in our database, around 4000 addresses. We do this by copying and pasting all of the addresses into the BCC field in Outlook. The problem we're having is that not all of the emails are copying into that field, there seems to be a limit on the field length. Does anyone know how to overcome this limit? Many thanks for any help, Gareth. Don't use Bcc. Instead, use a mail merge to send individual messages, = which will be more likely to make it through the recipients' spam = filter.=20 --=20 Sue Mosher, Outloo...

Nesting if Functions
I would like to use a nested if function to display a months work summary in a single area using a scroll bar. I have used this to good effect for small amounts of data tables but would like to extend it to displaying information covering 12 separate months. I know you can only nest an if function up to 8 times I believe. Is there a way around this that would let me display my 12 months of data I need to. Regards Don There is bound to be another way, using some sort of lookup probably. Can you give some details of the data, and what you need to do specifically, and expected resul...

How to draw a regression line on 7000+ dated data with Excel 2007
How to draw a regression line on 7000+ dated data using Excel 2007 Are your "dates" actual Excel dates or just text? With actual Excel dates, pre-2007 versions have no problem doing simple linear regression. To interpret the results though, you need to remember how Excel dates are stored: An Excel date is the number of days since 1900, so the numeric value of today's date is 39736, and the intercept of the regression will be the value of the relationship extrapolated to 30Dec1899 (since Excel mistakenly considers 1900 to be a leap year) "Gordon Lee" wrote: >...

Customized display order for fields
I need to define formats (i.e. bold, underline) for reference table fields. For example, let’s say reference table A contains three fields: Field1, Field2, Field3 and two rows. Reference Table A ID _Num Field1 Field2 Field3 1 Single Double Triple 2 Single Double Quadruple Forms need to know that for row 1 Double will be in bold and Triple will be underlined. However, for row 2, only Quadruple need to display in bold on forms, reports, etc. In the example above, what if I wanted the first row to be displayed on a form as Triple, Single, Double and the second row to be...

setting HasDataTable to true from C# VS2003
Hi there I am using Visual Studio 2003 C#, ASP, calling Excel, passing it data creating a chart (line) which it passes back as an image for my we app. ...but I need to display the DataTable below. In Excel I see i has a "Show Data Table" and from VB I can set HasDataTable, but from C how do I reference it??? I will appreciate any tips or direction! Thanx in advance!!! Many thanx Kind regards Ashley:confused -- Message posted from http://www.ExcelForum.com Hi there ...after doing some research, I don't think this is possible. I am using the Office Web Components (OWC), a...

I get wrong dates when i paste from a different sheet into a new s
Hello again, My problem now is when I open an old excel sheet with data on it and try to paste that info on a new sheet I get wrong information. example: I'm trying to copy dates from an excel sheet I have. The dates are for the year 2004. When I copy the data and paste it on a new sheet the dates I now see are 2000. I'm puzzled. What I find wierd is when I have clipboard open the information shows the correct dates but when it's actually pasted on the sheet it shows as 2000. I'm using Excel 2003, but I get the same thing when using excel 2002(xp) Thanks for your help,...

sum a field value in all querys
hello everyone How do i sum a value with another field (always the same one) in all my querys? Later, i would like to change that value to see the result in all querys. How do i do that? Please explain me best you can do cose i'm not an expert on access. Tks in advance Pedro Hi Pedro, What is the data type of the other field (always the same one)? The value you wish to sum it with - what data type is it? For example: if you had a query where one field was called rating and it was a long integer, if you wanted to add the value 4 to this you could create a calculated field in the que...

Accuracy of money fields in CRM 3.0
Hi, does anybody know how to change the accuracy of money fields in crm 3.0? Unfortunatly I cannot change the build in money fields precision. It is limited to two digits. New money fields can have a precision up to five digits but that will not work in my scenario. Any suggestions? J�rg Hi Jörg, Can you tell me exactly which field's precision you want to change? I could change the precision of some existing fields through: Customization -> <<entity name>> -> Attributes -> <<select the attribute>> -> Precision picklist. You can have up...

How to statically linking 2 files for 32 bit and 64 bit in VC++ 2005
I am trying to statically link a xyz.lib file to my 32-bit VC++ 2005 MFC app in order to use a .sys file. I have two xyz.lib files, one for win32 and one for amd64. I know I should add the .lib file to ['Property Pages' > 'Linker' > 'Input' > 'Additional Dependencies'] But which file do I add, how do I add both files, and how do I ensure the 32-bit app can use the linked .sys on 32-bit as well as 64-bit? Thanks! Hi, Just use the 32 bit lib, your app will run on 64 bit via WOW64. 64 bit development would involve setting up a new cpu platform, whi...

changed time and date and messed up appointments
Hi I recently went to UK from Australia, then to italy, I must have changed the time zones, and now my appointments are all over the place, Is there a way of getting them back to original times and dates. for the future is there a better way of handling time zones. i also use a pocket pc where i probably changed zones as well. thanks tim See the following MSKB article for a workaround: http://support.microsoft.com/?kbid=197480 -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the mes...

Setting Out Of Office
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I am having a problem setting out of office. I can connect to the Exchange Server (Exchange 2007 - I don't know what SP) and send/receive mail, access the GAL, view public folders, view free/busy information and other people's calendars, etc. However when I try to set Out of Office I get a message saying I cannot connect to the Exchange server. Any ideas where I can start to diagnose this problem. On 2010-04-17 03:31:46 -0400, Brendan_O_Rourke@officeformac.com said: > Exc...

Unable to set property required to True
I imported a modified form to a customer's system. The field I added to the window has the Required property set to True. However, when I imported the form, I got the following message, "Unable to set property required to True". The customer does not own the Modifier - only the Customization Site License - so I change can't the field property on their system. Why did it not import correctly? ...

dynamically referencing to another worksheet
Hi all, I'm relatively new to Excel but I'm working on a spreadsheet for work. Let's say I have a workbook with a separate tab (worksheet) called "exam1". On the first worksheet I can reference to a cell (let's say A2) in the other by using "=exam1!A2". Here's what I need to do though. On the first worksheet in column A, I will have a list of all the tabs that will eventually be included in the spreadsheet (e.x. A1 = exam1, A2 = exam2, A3 = exam3, etc.). Is there a way I can modify the code from the first worksheet above (=exam1!A2) to be dynamic? ...

List of stocks/investments in 2005
In my previous version of money I was able to get to a list of investments. I could scroll around through the dropdown at the top of the window and then make changes, delete them, etc. Has this feature been taken away? How can I delete an investment that I can't get to? In microsoft.public.money, Jack Addington wrote: >In my previous version of money I was able to get to a list of investments. >I could scroll around through the dropdown at the top of the window and then >make changes, delete them, etc. Has this feature been taken away? The drop-down list accesses tha...

Pushbutton control in worksheet?
Greetings, A long time ago, I managed to add a pushbutton control to a worksheet, and when that button was pressed by the user, the macro/function I had tied to the button wrote out a text file, the contents of which were strings that I built from data on worksheet 1 and wrote as lines on worksheet 2. I need to do a similar thing and was wondering if anyone had a quick answer as to how to do this. This also falls in line with my general question of how I add controls such as spinners or selection controls that allow the user to make a selection from a dropdown, for example, and have that se...

Command to reference previous worksheet
Hi, I have a macro whereby I copy the workbook and create a new file. There are many sheets, a, b, c....and I'd like the sheets in cell A1 to reference the previous sheet cell A1 + 1. Ex: in sheet "a", A1 = 30 therefore in sheet "b", cell A1 should show 31 and sheet "c", A1 = 32.... Some of the sheets will be deleted when it comes to the end of the billing cycle but I still want the subsequent sheets to continue with the sequence, ie, if "c" gets deleted, "d" should show A1 = 32. (A1 in "a" will always be changed upon a...

simply setting fonts in OL07?
OL07: tools/options/Mail Format/Stationery and Fonts (described as "to change your default font and style"). If I set a font here for new messages, and save/close it, and start a new message with Control-N, shouldn't it use that setting? It doesn't seem to. Yes, I restarted OL. I did just set a theme, but it says "always use my fonts" so I assume that means that the theme is not the culprit. yes, it should use the font you defined for the message format. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tas...

how do i set up a "send as attachment" shortcut in in a document
I would like to include a "hyperlink button" on an excel document that would enable the viewer to enter data, click on the button, and then have the document sent to an assigned email address as an attachment. Check out Ron's articles on working with worksheets/workbooks in email: http://msdn.microsoft.com/en-us/library/bb268022.aspx -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "SB47MASS" wrote: > I would like to include a "hyperlink button" on an excel document that would > enable the viewe...

Year + week => first date of week
Hi, I would like Excel to calculate the date for the first day (Monday) in a week.I have my sheet setup like this: Cell = A1 = Year = 2009 Cell = A2 = Week = 32 Cell = A3 =DATE(A3,1,1)+7*B3 this gives me the Excel date = 40038 (2009-08-13 Thursday) this is not correct. Correct Excel date for 2009 week 32 should be 40028 (2008-08-03 Monday). I live in Sweden and we use the ISO week that starts on Mondays (first week of the new year is the first week that contains Thursday). I don't know if it has got something to do with that the formula above does not work. Above formula is the one I ...

formula field: don't display anything when value is zero (using ;"
hello, I read this under the topic "How to get a formula field to total an entire table column, even if some cells in the column contain text or are blank" on the MVP-site and can't get it to work. I'm interested in the final bit, using ;"" Does anyone know how to do this? <start quotation>... Then in the total cell, press Ctrl+F9, and within the field braces {}, insert the following formula: { SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" } “Table1” in the formula refers to the name of the bookmark you've marked the ...

Date problem #6
I have a column containing 4/21/2004 10/7/2003 5/5/2004 Which I need to convert to UK dates. because of the variables(4/, 10/) I am unable to use mid etc in seperate columns. also =TEXT(f2,"DD/MM/YY")fails to work. Hi are these values 'real' date values. If yes a simple format should do -- Regards Frank Kabel Frankfurt, Germany "Gerry" <Gerry.Briant@goodrich.com> schrieb im Newsbeitrag news:9a4601c486b0$01ac1f40$a501280a@phx.gbl... > I have a column containing > 4/21/2004 > 10/7/2003 > 5/5/2004 > Which I need to convert to UK dates. > bec...

Month function
Hello, I have Excel 97 with the first service pack at home. I don't have an internet connection at home, so can't install the second service pack. Now that said... In A1 I have: 10/8/04 in A2 I have this: =MONTH(A1) A2 actually says "January" without the quotes. No matter what date I put in A1, A2 always says January. Is there any way at all to fix this? TIA!!! Cindy Hi is your value in A1 really a date value? -- Regards Frank Kabel Frankfurt, Germany CindyH wrote: > Hello, > > I have Excel 97 with the first service pack at home. I don't have an > ...