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 5 RepliesExtract data with differing date formats that I need to convert to something consistent that I can format as a date.
This is an example of data.... all with general format at the moment.
2/28/2013 2:48:53 PM
1/16/2013 10:48:50 AM
12/17/2012 11:59:49 AM
I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?
=LEFT(G9,SEARCH("/",G9)+7)
(The day portion of this date always has a leading zero).
I have these as input entered as text:
January 01, 2011 January 01, 2011 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010
but I need it in this DATE format:
MM/DD/YYYY
I need it as a formula to be entered into a cell in excel (not VBA)
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.
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.
I have some dates in text fomat on a worksheet that I need to convert them to true dates(Value). Here some sample of the text dates: '12/31/2011; Blank space12/31/2011.
Sub CnvtTextDate()
For Each Rng In Selection
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _
[code].....
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 4 Replies View RelatedHow 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 2 Replies View RelatedI am trying to convert a date to a particular format (mmm-yy) and then convert it to text.
On the spreadsheet I usually use Text(Today(),"mmm-yy") so I used the WorksheetFunction.Text to accomplish this but is there a VBA method to match this functionality?
I have this kind of date:
1/11/2014 (dd/mm/yyyy)
I want to convert it to text like this: 01112014
I use =text(a1;"ddmmyyyy") but nothing happens.
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: ...
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 ! ...
I am looking for a formula that converts:
20070413
to:
39185 (The excel date number for April 13, 2007)
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 2 Replies View RelatedMy 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.
I have some date data use MDY format. Want to convert to DMY format. But text to columns function doesn't work as those dates are text not numbers. How can I do the convertion?
examples:
3/14/19644/18/1964
I have some dates in Excel in different formats and I need to convert them all to a uniform date format of MM/DD/YYYY.
The data is in this format:
82012
8152012
52012
5152012
The days of the month don't really matter. It's the month and year that I need in a date format.
I have a situation where textual comments are entered into a cell where the comments are supposed to begin with a date in mm/dd format. Some enter all 5 characters but some enter m / d in if appropriate to the date, e.g., 9/6 vs 09/06.
I'm trying to take the left 5 characters and compare that to the current date - 7 to ID aged comments and highlight that cell. I currently do this by populating a variable LastUpdate (dim as string) with the first 5 characters, then run this IF statement.
If LastUpdate < Date - 7 Then
Range("O" & i).Select
Selection = "Comments over 7 days old."
End If
This works find except in situations where there is a zero for the first character after the "/", such as "11/05." The code doesn't properly calculate the date and assumes it's old. "11/5" works just fine. I thought I could try translating the variable into a proper date. I "borrowed" this formula from another post herein: =DATE(2012, MID(N12,1,FIND("/",N12)-1), MID(N12,FIND("/",N12)+1,2)). This works fine as a formula to translate the characters into a date but I don't know how to do this same thing in vba.
i am looking for vba macro to convert date format (indonesian language) to be text format, see this below :
sample (col.a)
after vba
09/10/14
09 Oktober 2014
26/12/11
26 Desember 2011
05/06/10
05 Juni 2010
etc...
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!
I have a column wherein the dates are treated as text , i want to convert that into dates.
View 9 Replies View RelatedWondering if there is a simple and concise way to convert a date into text that represents the day number?
So a cell containing “17/09/2006” simply reads as “Seventeenth”, 18/09/2006 becomes “Eighteenth” and so on...
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 2 Replies View RelatedI have a long date and time text value in a cell such as "2013/10/02 07:43:39.39", where the fractions of a second are very important.
I can use cdate([text]) to convert the text string to a value, but only if i omit the seconds fraction: "2013/10/02 07:43:39".
Otherwise i get a type mismatch error.
Is there any way i can get the whole date/time string converted to a date value? The only way i can think to do it it at the moment is to convert the date/time, then divide the seconds fraction by 86400 and add the 2 values. Is there a better way?
Secondly, i have a large number of these date strings, typically >30k lines x 7 columns. Each string is preceded and trailed by a [space] character.
I can strip these extra spaces no problem. And, i can "loop" to perform it, again no problem. But it's going to take time to perform and i'd rather not if i can avoid it.
I have a date range that looks like this (British date style, not US):
edit: I posted an image but it can't be seen for some reason:
Date Range
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10
Thus the first cell show 11 October 2010 to 17 October 2010 I want to return just the first day (the week commencing date):
edit: I posted an image but it can't be seen for some reason:
Week Commencing
11/10/2010
11/10/2010
11/10/2010
11/10/2010
I have written some VBA to the best of my ability but it seems clumsy using the FORMAT function. Is there a better of doing this with VBA. I can't seem to find a function in VBA which is the equivalent of the formula function DATE.
Code:
Sub ConvertTextToBritStyleDate()
' dd/mm/yyyy (not mm/dd/yyyy)
' Select the cells containing the dates (not the header)
[Code]...
Please refer to attached spreadsheet
I receive data from an external source and it displays what appears to be dates in column B.
In column G I apply a formula to display month/year info.
It turns out that some of the data in column B is valid date data, but other data imports as plain text and therefore I can't get the month/year info that I require.
I have attached just a small example.
I get this data monthly and it usually covers thousands of rows and therefore impractical to change manually.
I would like to know how can I convert a column P and T that contains these type of number into a proper way of date of DDMMMYYYY.
Like, if the cell in the column P has 8 digits then convert
19830425 -> 25APR1983
19910515 -> 15MAY1991
next cell till end of column P. Then do the same to column T.
I want to use a formula, in another cell, to convert ā€ˇ"Friday, ā€ˇ30 ā€ˇMay ā€ˇ2014, ā€¸ā€ˇ3:47:16 PM" to a value. I am using Excel 2003.
View 2 Replies View RelatedIs it possible to do the following with a formula in Excel...
I have a list of users with the dates they first logged into a system and the date they last logged in. I'm trying to group them together into segments so I can analyse them using a Pivot table and chart to see how often different groups are using the system, e.g. New customers in December, November, October, etc.
The trouble I am having is trying to convert the different human-readable text strings into a consistent number of weeks from today's date, e.g.
USER FIRST LOGGED IN LAST LOGGED IN
user 1 2 years 20 weeks 54 sec ago
user 2 44 min 7 sec 1 min 37 sec ago
user 3 49 weeks 2 days 17 min 3 sec ago
user 4 5 weeks 2 days 33 min 32 sec ago
user 5 38 min 9 sec 38 min 9 sec ago
user 6 5 weeks 3 days 1 hour 7 min ago
user 7 2 hours 17 min 2 hours 11 min ago
user 8 45 seconds ago 45 seconds ago
Is there a formula I can use to convert these human readble text strings into a number of weeks elapsed since today's date?
I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.
View 2 Replies View Related