Date conversion issue... again

Hello all,

I have a file that contains dates. I know that for example "12.10.2004" 
means "December 10th, 2005", however Excel treats as "Oct. 12th 2004". 
Nothing works: converting the format of the cells doen't help, the 
TEXT(A1;"mm.dd.yyyy") doesn't do anything because it works on the serial 
number instead

Any suggestions?

thanks a lot
0
Dimmer (7)
7/11/2005 9:28:02 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
368 Views

Similar Articles

[PageSpeed] 14

in some other empty cell (without any formt) type this
=(SUBSTITUTE(A1,".","/"))+0
you may get the date number
format it as you like

do you get what you want;.


Dimmer <Dimmer@discussions.microsoft.com> wrote in message
news:662861A5-5A8E-48A2-9D92-4E917DE3F908@microsoft.com...
> Hello all,
>
> I have a file that contains dates. I know that for example "12.10.2004"
> means "December 10th, 2005", however Excel treats as "Oct. 12th 2004".
> Nothing works: converting the format of the cells doen't help, the
> TEXT(A1;"mm.dd.yyyy") doesn't do anything because it works on the serial
> number instead
>
> Any suggestions?
>
> thanks a lot


0
R
7/11/2005 10:14:03 AM
Hi,

You can use the following function to convert the values:

Function ccDate(myDate As Date) As Date

    dDate = CStr(myDate)
    
    myValues = Split(dDate, "/")
    
    myMonth = CInt(myValues(0))
    myDay = CInt(myValues(1))
    myYear = CInt(myValues(2))
    
    ccDate = DateSerial(myYear, myMonth, myDay)
    
End Function


I am recruting new members for my forum below... The site went live this 
weekend and if you wish to take part, please, join me there so that we can 
exchange some ideas.

-- 
Regards
Robert
Find me at www.msofficegurus.com - be part of it!

0
cmart021 (10)
7/11/2005 10:25:04 AM
I only noticed that you used a "." instead of "/" in the date. In this case, 
change the following line:

myValues = Split(dDate, ".")

Alternatively, you can use an extra argument so that you can indicate the 
separator.


-- 
Regards
Robert
Find me at www.msofficegurus.com - be part of it!


"cmart02" wrote:

> Hi,
> 
> You can use the following function to convert the values:
> 
> Function ccDate(myDate As Date) As Date
> 
>     dDate = CStr(myDate)
>     
>     myValues = Split(dDate, "/")
>     
>     myMonth = CInt(myValues(0))
>     myDay = CInt(myValues(1))
>     myYear = CInt(myValues(2))
>     
>     ccDate = DateSerial(myYear, myMonth, myDay)
>     
> End Function
> 
> 
> I am recruting new members for my forum below... The site went live this 
> weekend and if you wish to take part, please, join me there so that we can 
> exchange some ideas.
> 
> -- 
> Regards
> Robert
> Find me at www.msofficegurus.com - be part of it!
> 
0
cmart021 (10)
7/11/2005 10:35:02 AM
you could try
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))
and format the cell with the date format you prefer

-- 
Regards
Roger Govier
"Dimmer" <Dimmer@discussions.microsoft.com> wrote in message 
news:662861A5-5A8E-48A2-9D92-4E917DE3F908@microsoft.com...
> Hello all,
>
> I have a file that contains dates. I know that for example "12.10.2004"
> means "December 10th, 2005", however Excel treats as "Oct. 12th 2004".
> Nothing works: converting the format of the cells doen't help, the
> TEXT(A1;"mm.dd.yyyy") doesn't do anything because it works on the serial
> number instead
>
> Any suggestions?
>
> thanks a lot 


0
roger5293 (1125)
7/11/2005 11:22:58 AM
Hi 
I never used such a function before... how do I proceed?... 

thanks

"cmart02" wrote:

> I only noticed that you used a "." instead of "/" in the date. In this case, 
> change the following line:
> 
> myValues = Split(dDate, ".")
> 
> Alternatively, you can use an extra argument so that you can indicate the 
> separator.
> 
> 
> -- 
> Regards
> Robert
> Find me at www.msofficegurus.com - be part of it!
> 
> 
> "cmart02" wrote:
> 
> > Hi,
> > 
> > You can use the following function to convert the values:
> > 
> > Function ccDate(myDate As Date) As Date
> > 
> >     dDate = CStr(myDate)
> >     
> >     myValues = Split(dDate, "/")
> >     
> >     myMonth = CInt(myValues(0))
> >     myDay = CInt(myValues(1))
> >     myYear = CInt(myValues(2))
> >     
> >     ccDate = DateSerial(myYear, myMonth, myDay)
> >     
> > End Function
> > 
> > 
> > I am recruting new members for my forum below... The site went live this 
> > weekend and if you wish to take part, please, join me there so that we can 
> > exchange some ideas.
> > 
> > -- 
> > Regards
> > Robert
> > Find me at www.msofficegurus.com - be part of it!
> > 
0
Dimmer (7)
7/11/2005 1:57:04 PM
Check out what format is in your Windows settings.

<Start> <ControlPanel> <Regional Settings>

-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Dimmer" <Dimmer@discussions.microsoft.com> wrote in message
news:662861A5-5A8E-48A2-9D92-4E917DE3F908@microsoft.com...
Hello all,

I have a file that contains dates. I know that for example "12.10.2004"
means "December 10th, 2005", however Excel treats as "Oct. 12th 2004".
Nothing works: converting the format of the cells doen't help, the
TEXT(A1;"mm.dd.yyyy") doesn't do anything because it works on the serial
number instead

Any suggestions?

thanks a lot


0
ragdyer1 (4060)
7/11/2005 3:11:58 PM
Reply:

Similar Artilces:

Can I change the calendar year beginning date?
Our fiscal year runs July 1 thru June 30 of each year. I need to track attendance, gas cards, etc for monthly reports. I have a HUGE table with a gazillion queries and reports for each one. I need one report to reflect quarterly output for our fiscal year as stated above. Can I make this report do that? Thank you! Create a query to use as the source for your report. In query design, type this into the Field row: FinYear: DateAdd("m", -6, [InvoiceDate]) replacing InvoiceDate with the name of your date field. This yields 2007 for all dates in the 2007/2008 financial year...

Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik this: 10/15/1999 14:34 When I use the concantenate feature my date looks like this: 36448.6073611111 I tried to format the call every which way - but I cannot get it t look the original. Feeling really silly for even asking.. thanks all for your help -- Message posted from http://www.ExcelForum.com Hi bleu808! Use: ="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm") -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "bleu808 >" <<bleu808.189yij...

Show a date 30 days out
I have a feild that I would like to show what the date would be in 30 days for a feild with a date in it [Sold Date]. Sold Date feild would be 1 Jan 07 so the feild would show 30 Days later 31 Jan 07. I did =[Sold date]+30 and it did not work Thanks how about dateadd: dateadd("d", 30, [Sold Date]) -- steve. "KAnoe" <KAnoe@discussions.microsoft.com> wrote in message news:DE9BDDAE-5C84-4A52-8185-DAB6C3167E7A@microsoft.com... >I have a feild that I would like to show what the date would be in 30 days > for a feild with a date in it [Sold Date]. > ...

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

Excel template issues
I have a user that has a excel template on his network home dir NW 5.1 Server he says that after he creates a new file from that template - he goes to open and it tells him read only access running Excel 2000 thanks ...

Office XP Exel
Scenario: User's A,B, C, and D all have access to an excel XLS on a 2003 server. Users A & B have modify rights, users C&D only have read rights. If user A updates the file, the general tab in properties reflect the exact time the file was modified. After user A saves and closes the file, user D goes in. The changes are there, but in the properties general tab, the modify date in an old date (probably the actual creation date). Is this normal ? Any idea's ? ...

Line Chart with dates in 5 day working week only
Hi, Trying to format a chart so that only the 5 working days of the week are displayed on the x axis. The source data only has the five days (e.g. 05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005 etc etc) So I have missed out the weekend dates. When I create the line chart however, the weekend dates appear automatically and just show no point on the chart, therefore there is a longer line between Fridays and Mondays!! Hope this makes sense. Does anyone have any ideas on how to change this? I have tried looking at Tools-options-chart and cannot seem to turn...

Public folder issue, "messaging interface has returned an unknown error"
I'm running a SBS2003 domain and just added a public folder and a customized form for entry into the public folder. All of our clients (all XP Pro, Outlook 2003 with all updates) can get to that folder and use the form without any difficulty except for one. On one computer I get the classic "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." error message. I have tried a repair install, no change. I have tried making a new profile, no change. I have tried adding / removing his PST file (currently he's not even using a pst file...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

Time Conversion
I need help in converting time. I am using data from a time clock which currently formats time as 1.5. When I input this data into a cell how can I have it formated to read 1 hour and 30 minutes (1:30) T.I.A. Ed What you could do in an adjacent row or column is format them as Time and then using a formula, divide the input time by 24(hours in a day). >-----Original Message----- >I need help in converting time. I am using data from a time clock which >currently formats time as 1.5. When I input this data into a cell how can I >have it formated to read 1 hour and 30 minut...

Terminal Servers in Cluster - Login/Profile Issues
Hello Gurus, Currently I am having an issue with logins and profiles as per details below. Server/Network Configuration Details: 1 X Windows Server 2008 Std FE 64bit (DC) 2 X Windows Server 2008 Ent 32bit Terminal Servers User Profiles are Roaming and exist in: \\SBSERVER\Profiles on the Domain Controller and C:\Users\ on the Terminal Servers. Approx 50 users. Problem Description Error messages when logging in the terminal servers: Your roaming profile is not synchronized correctly with the server. Windows will load your previously-saved local profile instead. See the pre...

Issue with Loan Accounts
Hi Everyone, I have been having this issue for a while - but just now trying to figure out what's happening. I have several loans (including car, equity, etc.) which I have set up on Money 7. Sometimes the interest and principle are calculated correctly - but more often, the entire sum of the payment is allocated to principle (as viewed in the account register). I've check to insure that the loan is set up okay - and it looks okay to me. Any ideas what's happening to me? Any help appreciated. Thanks. Patrick Hi, I was really hoping someone would respond to this. My ...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Multiple Language Issues
Hi all, Has anyone successfully manually updated a non-English supported 5.5 to 2003? (The reason for the manual update was a broken admin account.) Mail and contacts were all exported to PST files, and then imported to 2003. However, although the text of the messages is fine (English and Russian), the text for contact names and message subjects gets gorched if they contained any Russian text. Does anyone know the correct sequence to follow to take messages and contacts in Russian (or other languages) from 5.5 to 2003, while retaining the non-English subject lines and contact names? ...

quicken file conversion?
how can i convert the data for two mutual funds from quicken to money 2003. i don't want to convert all old data of every acct in quicken 2003. any ideas welcomed ...

Calculating dates #3
Can anyone tell me what I should use (in the way of helper cells) to take any date (mm/dd/yyyy) and turn it into that same month and day for specific year? For instance, turn... 10/12/2009 into 10/12/2010 and 4/6/1998 into 4/6/2010 I'm trying to determine anniversary date based on start date and do it starting in 2010. TIA -- Jordon Try this: =3DDATE(2010,MONTH(A1),DAY(A1)) Assuming your date is in A1. Hope this helps. Pete On Jan 5, 5:35=A0pm, Jordon <jordon@REMOVE~THISmyrealbox.com> wrote: > Can anyone tell me what I should use (in the way of helper cells) > to tak...

how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show just the date and not the time. Going into format and clicking on the date and changing doesnt work. Probably the cells aren't really dates, but text. You can check with the ISTEXT() function. If they are text, formatting doesn't have any effect. -- Kind regards, Niek Otten Microsoft MVP - Excel "bondam" <bondam@discussions.microsoft.com> wrote in message news:F4D61EC1-30E2-4723-89E1-F47B545818EE@microsoft.com... >I want to format a column that contains date and time and I want it t...

Money 2005 Password Issue
I have been using Money 2005 for about 8 months and suddenly I cannot sign in using my usual hotmail.com passport. The error message is "the e-mail address or password is incorrect". However, I can sign in using the same e-mail address/password pair to hotmail and MSN Money in IE. I've tried KB Q331123 but no luck. Any ideas? More details: 1) I can get into my Money file in offline mode 2) I even tried to change passport password. The new password works in IE, but not Money. "MM" wrote: > I have been using Money 2005 for about 8 months and suddenly I cannot...

Native Resolution Issue
I have 298 laptops my section supports and recently we started running into issues with higher native resolutions. Some of our users don't like the higher resolutions because it makes everything too small, so we adjust them to lower reslutions, but the settings revert back to the native resolution whenever the laptops go to sleep, get removed from the dock or if someone else logs onto them. We use HP and Dell laptops with various models. Is there one setting in Windows XP Pro SP3 where we can "turn off" native resolution? Thanks for any help -- J Wilson ...

Issue opening documents in MSOffice 2003
Hello I wonder if anyone can answer my question? I have a new laptop running Windows 7 Pro with Office 2003 and I notice that when I seek to open an Excel or Word file with a .doc, .docx, .xls or .xlsx extension, that Excel or Word opens the file but also an empty window of opens at the same time, e.g. I have two windows of Excel open, one with the document I want and the other is empty. Is this a default action for any reason or a bug? This is more of an anomaly than a major issue but I am curious? Any advice would be welcomed. Thanks Martin O'Shea. "...

Calculate age as of a date certain
Employee benefit enrollment requires the age of each employee as of a specific date, such as 1/1/2005. Given the date of birth, how would this be calculated? -- Joe S. Hi see: http://www.cpearson.com/excel/datedif.htm#Age -- Regards Frank Kabel Frankfurt, Germany "Joe S." <JoeS@discussions.microsoft.com> schrieb im Newsbeitrag news:0EC4F9E5-2E60-448A-A107-7B085BC764C5@microsoft.com... > Employee benefit enrollment requires the age of each employee as of a > specific date, such as 1/1/2005. Given the date of birth, how would this be > calculated? > -- > Joe ...

Conditional formatting a date range
If I have a column of dates that are manual entered what is the formula to conditionally format them based on a date range of three months before the current date to the current date and another three months after the current date to the current date? Assume the dates are in column A, starting with A1. Highlight all the dates, with A1 as the active cell, and click on Format | Conditional Formatting. In the dialogue box you should select Formula Is rather than Cell Value Is and then enter this formula: =3DAND(A1>=3DTODAY()-91,A1<=3DTODAY()) Click on the Format button and choose the ...

Date/Time stamp in Memo Field
Hi all..is it possible to programmatically insert Now() when text in a Memo field becomes edited? Thanks for all help! If it has to be entered directly in the memo field tetxbox: Private Sub MemoFieldName_AfterUpdate() If IsNull(Me.MemoFieldName.OldValue) Then Me.MemoFieldName = Now & " " & Me.MemoFieldName Else Me.MemoFieldName = Left(Me.MemoFieldName, Len(Me.MemoFieldName.OldValue)) & " " & Now & " " & Right(Me.MemoFieldName, Len(Me.MemoFieldName) - Len(Me. MemoFieldName.OldValue)) End If Me.Dirty = False End...