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.
Extract 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).
Reference the archives at: http://www.mrexcel.com/archive/VBA/17448.html
How can I modify this sub to skip cells that are empty? I only want cells that are populated within the range to actually display the comment indicator, with the cell text transferred to the comment.
Ideally, I'd also like the text to be moved (not just copied) to the comment and leave an 'X' in the cell, in place of the text that was transferred to the comment, but I'll settle for the action above.
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:= _
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.
I 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?
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 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.
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.
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?
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!
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.
I 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 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)
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.
Is 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?
