Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Convert Date Separators From Periods To Slashes


I have spreadsheets with a lot of dates where the date separators used are periods . instead of slashes /. For example, 12.02.2008 is what shows in a cell. Where I live (Australia) that means 12 Jan 2008. My need is to have Vba code that converts these to dates; e.g. 12/02/2008 or 12-Jan-2008, etc. I can manually select the cell or cells, press Ctrl+H, replace the period characters with slash characters and it works correctly - I get 12/02/2008 which I can then manipulate as a date. However, if I record a macro of the above actions, when I run the code the result I get in the cell is 2/12/2008 i.e. 2 Dec 2008. The code recorded is:

Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The only time the code gives the correct result is when the month is greater than 12; e.g. 23.02.2008 is converted to 23 Feb 2008. My computer is set up with regional settings to Australia and date format day/month/year.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Enter Date Without Separators
I'd like to be able to enter a date in excel as just the characters without the dashes and have it correctly recognized -- such as 05142006 changes to 5/14/2006. I have a great deal of data to enter and it would go much faster if I could do it in this format. Is there a VBA script or function of excel that I could use for this? I've tried looking around on here a bit and in the program settings,

View Replies!   View Related
Convert Imported Dates & Add Periods Based On Conditions
I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd

I desire to look in column "E" and I have 3 different variables. "J", "K", "L".
If column "E" has a j add 30 to the years; K add 35 years and L add 38 years.
for Example: 19630923 j = 19930923 k = 19980923 L = 20010923

View Replies!   View Related
Counting Occurrence In Date Periods
I want to count the number of sales in three periods. prior 7 to 12 months, prior 4 to 6 months, and over the last 3 months.

I have three letters that occur in column B of sheet 2.

A for active, P for pending, and S for sold.
The date of activity appears in column C for each event. It is in mm/dd/yyyy format. Currently I have over 5000 rows.

I would like to total the sales for each period and place it on sheet 1.
can you help or point me to the right place to read up on it. I can get the information by using a pivot table but there has to be a faster way.

View Replies!   View Related
Take Slashes Out Of Dates
Is there anyway to take the slashes out of the dates and keep the same number format? example...11/11/1974 should then = 11111974

View Replies!   View Related
Dates And Slashes
i would like to input say 201005 in a textbox and on exit it should read 20 / 10 / 05 ie dd / mm / yy
how this can be achieved using VBA.

View Replies!   View Related
Extra Separators In Concatenate Range
Return a list of each " case" that includes "item X" (both cases and items are stored as text). Each "case" has a list of associated items, which are stored in another worksheet. "Item X" may be associated with many cases (e.g. not unique to a single case).

The results should look something like this (item in one column, concatenated list of cases in another column): ....

View Replies!   View Related
Format TextBox With Thousands Separators
We have created a userform with several textbox for numbers. We'd like to show them with a format similar to "###,##0.00" and allow the users to enter numbers without commas, but always show the textbox formated. Is there any special way to manage this format? As the textbox is a "text" value, how can we manage the input and the numbers? Should we always replace the "," withi a nullstring? Example:

Private Sub tbCakPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
tbCakPrice.Text = Format(Val(tbCakPrice.Value), "######0.00")
tbPxCakCnFCgl.Value = Format(Val(tbCakPrice.Value) + Val(tbFleteRealCgl.Value), "########0.00")
End Sub.......................

View Replies!   View Related
Display Dates Without Month, Day & Year Separators
I have the below find and replace code that is now working. I double checked the syntax and can't find a reason why. The errors I get are Overflow and 400.

'format column with custom date format.
Public Sub formatDate()
Dim charHold As Date
What = m / d / yyyy
repl = yyyymd
Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

View Replies!   View Related
Convert Coded Date Into Common Format Date
I'm in the military and for maintenance we use the Julian date the format goes as follows "09357" that is simplified as 09 = the year and 357= the three hundred and fifty seventh day of the year. so 09357= 12,23,2009..

so on to my question:

how can i make excel auto convert the julian date to the regular date and vice versa?

View Replies!   View Related
Convert Number Representing Date To Real Date
I have an 8 character number that I am trying to segment and change into a date.

The current format is: YYYYMMDD, for example 20071003 (today's date)

I am would like to change it to read 10/03/2007 or another easily identifiable date format.

View Replies!   View Related
Convert A Text Date Into A Proper Date
I have a column of dates in thie format " January 5 03:09:36 2009" which i need to convert into a proper excel date that i can do further calculations on (adding up things, graphs etc based on dates).

Note the space before the month name. I thought about doing a left(cell,xx) to get it but that isnt going to work with the space infront and the variable length of month names.

I do not necessarily need the time included, it may be useful at some point if its easy to keep it as part of the data, if not i can live without it.

View Replies!   View Related
Convert Date As Text To Actual Date
I've been sent a spreadsheet in which one column represents dates, but they have been entered as plain text, MM/DD/YYYY (ie 12/31/2008). I ultimately need to import this as a CSV into a program that only understands dates in the format DD/MM/YYYY.

I'd ideally like excel to recognise this field as containing dates, but I can't figure out how to do that, or even how to swap the days and months around as text. I'm using 2007.

View Replies!   View Related
Convert Solar Date To Gregorian Date
How can I create a UDF which convert the Solar Date "1386/9/10" to Georgian Date "2007/12/01".

View Replies!   View Related
Move From Seperate Columns On Sheet2 To One Column With Forward Slashes "/" Between Sheet3
I have three columns (9, 10, & 11); text, currency, and text and I am trying to move them from seperate columns on Sheet2 to one column with forward slashes "/" between them on Sheet3. The attached code does this, however I lose the dollar sign and commas.

View Replies!   View Related
Convert Date From Dd-mm-yy To Mm-dd-yy
A friend sent me over a file, but the dates are in "European" format. The cell shows a date as "01.10.05" I'd like to convert it to 10/01/05.

View Replies!   View Related
Convert Text To Date?
I've been playing around with various formulas to try and get the following to work, but doesn't do it.

What I have is a list of 'dates' in one column, but all seem to have an extra 'space' (so aren't recognised as a date). for example: 31/01/05

Essentially I'd like to convert to a date to find which is the most recent. So far i have tried to use: ...

View Replies!   View Related
Convert ActiveRange To Date
I would like to convert the active range to a date via the text to column method via a macro:

Select column or range of data with dates
Data Menu
Text to Columns
Delimited
Remove all Ticks from option boxes
Next button
Select Date
Select format YMD or YDM (not sure what the format is from your posted example)
Finish Button

I have the following code which i thought should work, but it doesnt.

View Replies!   View Related
Convert A US Date Format In To UK
I am trying to convert US dates (with times e.g mm/dd/yy mm:hh) to UK date format I do not want to chage the time.


View Replies!   View Related
How To Convert A Text To Date
In the example below , i have in column A data which represent different dates, column B are the currency rate and column D has imported text figures which are not recognized by excel as format dates neither as serial codes !

Though trying to index match the column D data in column A to return the rate of that date , the result is an error ! ...

View Replies!   View Related
Convert The Date Format
I am having some issues converting the dates in column A on the attached spreadsheet to the mmmm format. I cannot get the dates to change format at all so I was hoping someone might have some ideas why. I have tried everything I can think of. The only thing that has worked so far has been to retype the date in the cells. I have 32000 rows of data I have to convert.

View Replies!   View Related
Convert A Date To A Label
I am trying to convert a date to a label. The following is what I am trying to do:

1/9/09 (formatted as date) converts to date value of 39822

How can I copy that cell & paste so that it will actually read 1/9/09 and not actually put the 39822 value in?

View Replies!   View Related
Convert Date Format
I have imported date field values from ClearQuest(IBM Product) into Excel worksheet. after import the data display format in the excel looks like

Thursday, February 19, 2009 9:11:42 AM

Now I need to calculate the age of the defect in number of days by using the formula

=NOW()-J26 where the cell J26 contains the above data . However I always get the display as #VALUE in the cell.

I changed the field format for cell J26 to custom dddd, mmmm dd, yyyy h:mm:ss AM/PM but still does not work.

My question now is- how do I convert dddd, mmmm dd, yyyy h:mm:ss AM/PM to mm/dd/yyyy h:mm:ss AM/PM so that I would be able to find the age of the defects?

Or is the a way to change the format for NOW() to something dddd, mmmm dd, yyyy h:mm:ss AM/PM?

View Replies!   View Related
Convert Date To Workday
If I have a date in cell G6 of 10/7/08, how could I get the workday of 5 in cell H6? I tried the workday function, but have been unsucessful so far.

View Replies!   View Related
Convert Date To Day
I have a lon list of dates in the format dd/mm/yyyy. I can change the format to DDD to show the day of the week. What I want to do though is make that permanent, not just in the format.

View Replies!   View Related
Convert Text To Date ..?
How would "April" be converted to "4/1/2009 8:00:00 AM"? The months will vary, but the time will always be 8:00:00 AM.

View Replies!   View Related
Convert Date To Week
I have a date which is recorded in the mm/dd/yyyy format, and in the adjacent column I would like to determine which week in the month this date is, from 1 to 4. So for example if the date was 07/02/2009, the week would be 1. For simplicity's sake I figued I would just have days 1-7 as week1, 8-14 as week 2, 15 - 21 as week three, and 22 on up as week 4. If there is a more accurate way of doing this, say that would be able to sart counting from the first full week of the month, then I would be very interested, but that seems unreasonable.

View Replies!   View Related
Convert Number To Date
i have number in database 1 and want to convert as date in database 2

04079219920506
04089219920507
04099219920510
18730120010729
18740120010801
18750120010804
18760120010805

result in database 2

407/9206/05/1992
408/9207/05/1992
408/9210/05/1992
1873/0129/07/2001
1874/0101/08/2001
1875/0104/08/2001
1876/0105/08/2001

is it got any formula to convert this number from database 1 to database 2, so far i has attach sample files..

View Replies!   View Related
Convert Text To Date
in columns I and J i have text which reads as for example 20090619. I need to convert this to a date format as in dd/mm/yyyy. A lil macro would help loads. i also have some other spreadsheets that have the date as 16 jun 2009 and i have to basically go into the cell and then hit enter for it to become formatted like the dd/mm/yyyy.

View Replies!   View Related
Convert A Date To Text?
If I have =TODAY() in cell A1 with the formatting ddmmyy (which returns 141108 ) how would I covert it to a number in another cell. SEE ATTACHED

View Replies!   View Related
Convert Text To A Date
I am looking for a formula that converts:

20070413

to:

39185 (The excel date number for April 13, 2007)

View Replies!   View Related
Convert Date To Number
I saw the =WEEKNUM function introduced to Dave196. I tried it but I do not get the desired result. I get "# NAME?" What can I be doing wrong? Also, how can I have Excel take a date and convert it to a number? I want to convert a date into an invoice number (eg. 4/5/07 ===> 20070405)

View Replies!   View Related
Programattically Convert Date To Month
I have a column of dates that I would like to convert to just their Month names so 1/1/2009 would be "January". I am going to use this column in a pivot table as a filter so I need to actually replace the date with the month name. I would do it manually, but there are close to 32000 rows and every month it will grow even more. Is this possible with a macro? I attached a sheet with some sample data.

View Replies!   View Related
Convert Date Looking As Text To Dates
I have a column wherein the dates are treated as text , i want to convert that into dates.

View Replies!   View Related
Convert Date Time Format
I have a coloumn of cells in the format Dec 12 2005 07:04 PM
I need to convert them to the format 12/12/2005 19:04



View Replies!   View Related
Convert Date Seperated With Dot
I have two ways to export Forex data to Excel for analysis by another trading program. This exported data appears either as mm/dd/yyyy (in which case the cell alignment is inconsistent for some reason) or yyyy.mm.dd and I need to convert it to either yyyymmdd or dd/mm/yy.

I have tried the Data>Text to Columns Wizard without success and I have read through various other threads here, but they seem to all want to convert "in the other direction" !

View Replies!   View Related
Convert Date Format To Text
I am trying to make a very simple macro but get stuck in de Excel date format. When using the date "=TODAY()" in one cel it is no problem but I want to integrate it in the CONCATENATE function.

I want to create this text "(WG 01-01-2006)" but then with the actual date of that day of course, which can be inserted in de active cell.

Whatever I do, copy, pastespecial, reed text with RIGHT, change format on an temporary cell etc. Excel keeps returning the number in Excel date format counting from 01-01-1900. Nice for calculating but I want the date text!


View Replies!   View Related
Convert A Date In Specific Format
I'm trying to convert a date in the following format: 19970121 to 1/21/1997 and am having trouble getting rid of the leading zero in the final version. I also need it to pick up the 2 digits in months that have them from 9 (Sept) through 12 (Dec), though, for use in a VLOOKUP function. Can this be done at all?

Here is the code I've been using so far that doesn't entirely work for what I need it to do:

=CONCATENATE(LEFT(RIGHT(B2,4),2),"/",RIGHT(B2,2),"/",LEFT(B2,4))

where B2 refers to the column the original date form (19970121) is in. I'm still stuck with the leading zero in this code.


View Replies!   View Related
Convert Date Into Day Of Week
I have a form that users input the date (not just todays date) I want another cell to take that date and input the corresponding day of week.

example:

user inputs 03/10/09 into cell h17 I want cell A12 to automatically have the corresponding day of week (wednessday)

View Replies!   View Related
Convert Text String To A Date?
My database has 6 fields names and I have created the following code to capture and post data to the database form a userform. It works but I am sure there is a more efficient and elegant way to do this. The two areas I would like advice are:
1. converting the date string to a date.
2. the line of code where I subtract line6 from line4.

View Replies!   View Related
Convert From A Specific Date Format
If A1 is 81209, how can I use functions in B1 to convert it into DEC/09/2008?

View Replies!   View Related
Convert Text Cells To Date
i attached a spreadsheet with the column that needs to be converted to date. When i try to format cells i get a return of ##############. I tried changing to number and text to columns and still get the same result. I need it to look like 08/16/2008 ect.

View Replies!   View Related
Convert Number String To Date
I am trying to convert a number string date that has been stored in a database to a readable date time in an excel pivot table.

1260983366 needs to look like Wednesday, December 16, 2009, 10:09 AM.

The converted date could also look like Wed, Dec 16, 09, 10:09 AM.

View Replies!   View Related
Convert Date To Text Format
Is there a way to convert date to text of format (number stored as text) either directly using excel formulas or VB macros. We've a requirement to pass today's date as a string in the format of "number stored as text". For this, i've in cell A1 a formula today() which gives today's date 10/15/2007 and I'm looking an output of '20071015 in cell A2 and this should be treated as text with a symbol " ' " before 20071015.

View Replies!   View Related
Convert Textbox To Date Type
I have textbox for entering the date.But I don't know why when the code excuted, it displayed "type mismatch"?

Dim datebegin As Date
dateBegin = CDate(txtBegindate.Value)

View Replies!   View Related
Convert Non-US Dates To US Date Format
I am parsing a string which contains (European) dates:

"10/08/2005,11/08/2005,12/08/2005,13/08/2005,14/08/2005,".

When broken into individual cells, dates before the 13th of the month are recognised as "American" dates and display as "08/10/2005", "08/11/2005", 08/12/2005" etc. with automatic date format. Dates from the 13th onwards are left in general format as "13/08/2005", "14/08/2005".

I have written a macro that converts each date individually to its correct Excel serial number, but I can't help thinking that there must be a simpler way, and that I cannot be the first to have asked this!

View Replies!   View Related
Worked Hours Between Two Periods
I'm trying to calculate the total hours worked for two given periods over a shift , which can span two consecutive days ie. start 15:45 and finish at 00:15 the next day. Hours worked between 6am and 6pm are paid at standard rate, whilst hours worked between 6pm and 6am attract penalty rates. Hours are cacluated in 24hr time

I have attached a copy of the timesheet that we use so you can see exactly what I'm trying to achieve, and included most of the shifts that we have.


View Replies!   View Related
Allocating A Value To Different Time Periods
I got the following table.

http://img163.imageshack.us/img163/2972/oldnf.jpg

What formula could I use so that the amount under STP premium would display at the right cell under different time periods depending on the time of the trade date, just like the following images?

http://img687.imageshack.us/img687/5720/newcg.jpg

http://img168.imageshack.us/img168/413/new1zk.jpg

View Replies!   View Related
Delete Periods From String
I am having trouble writing a Macro to delete multiple periods at the end of each cell. Each cell looks simiar to this: Blah Blah Blah................... I am using the InStr function to find the first period but it returns a 0 everytime. Here is the code I used to find the period. I want it to delete everything after the first period and store the value back in the same cell. Right now it just stores blanks.

Sub PeriodRemover()
Dim NewContract As String, Contract As String
Application.Calculation = xlManual
i = 2
Sheets("Product Listing by Contract").Select
Contract = Range("A" & i).Value
Do Until Contract = Empty
NewContract = Left(Contract, InStr(Contract, "."))
Range("A" & i).Value = NewContract
i = i + 1
Contract = Range("A" & i).Value
Loop
Application.Calculation = xlAutomatic
End Sub

View Replies!   View Related
Pull Values Between Two Periods
I have a text value which consists of two periods ".". I want to pull the value in the middle. eg. From the text value in a cell "oakland.california.usa", I want to pull "california" in another cell. All the values in column are in similar format.

A B
1 oakland.california.usa california

Hope this is not duplication of any pre-existing thread, if it is, I probably don't know the correct keywords to query.

View Replies!   View Related
Convert Date To Month Running Number
how to convert date variable to month in running number. the starting date = 1996 Jan =1 and 1996 Feb =2 .... and so on.

as i plan to generate p table report for view more then 12 month data.....

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved