Formatting Text To Dates?
Apr 12, 2013I have an excel spreadsheet that contains dates and times however it appears that some of the dates are in text format Is there a way i can format the data fist so they are all dates?
View 5 Replies
ADVERTISEMENT
Date Formatting In VBA :: Dates That Are Imported As Text
Sep 12, 2007to change the format of some dates that are imported as text.
In a previous request ...
[url]
I was shown a way to convert dates correctly.
This solution works perfectly when carried out manually, but doesn't work if a record a macro and then replay it - only some of the dates are converted.
Here is a small sample of the dates I'm working with...
04/09/2008
05/10/2007
08/10/2007
14/09/2007
15/09/2007
17/09/2007
17/09/2007
17/09/2007
19/03/2008
20/09/2007
21/09/2007
21/09/2007
25/09/2008
27/09/2007
28/09/2007
28/09/2007
And here's the recorded macro, deliberately left exactly as is
Excel 2007 :: Unable To Convert Text Dates To System Dates
Oct 21, 2011I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
Concatenate With Text And Dates Where Some Dates Cells Are Blank?
Nov 14, 2013I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:
=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")
It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.
Convert Imported Text Dates To True Dates
May 18, 2007I have a very large spreadsheet that comes out of SAP and it brings out the majority of cell entries as text ie putting a ' at the begining of any number. the problem that i have is with the cells that have a date in them ie '19/04/07. I have tried varioous methods to remove this and turn the cells into date format but to no avail.
Also i do not get the option to convert text to number
Pass Control Dates To Cells As Dates Not Text
Feb 13, 2008I have a WAY to complicated excel form that I'm going to move to Access but I need to have this working in the interim. The form has a dynamic element, the user can choose the number of items they are ordering and it unhides the required number of text boxes. To keep the scripting down a did a little work-around to update the database using a loop through the form controls:
'Save the main row
MainOrderRow = ActiveCell.Row
LastRow = MainOrderRow + (ExtraOrders - 1) ' Calculate the last row
ExtraOrderNo = 1
For DBRow = MainOrderRow To LastRow
' Save the text box name with the extra order number
k = "txtDescriptionMul" & ExtraOrderNo & ""
l = "txtQuantityMul" & ExtraOrderNo & ""
m = "txtTotalValueMul" & ExtraOrderNo & ""
n = "ComboBoxUnitMul" & ExtraOrderNo & ""...................
Search & Replace Real Dates & Text Dates
Aug 9, 2008I have a macro that searches and replaces dates (both text and date formats) in all files in a folder. It works if I initiate it from the VBA Editor, but not if I initiate it from the file.
It seems that the worksheet is not activating. Please take a look and let me know what I can try.
Things I have already tried: Window methods, such as minimizing the workbook that contains the macro, activate next, etc. Also putting the macro in Personal.xls and retrieving the values from the other file.
Where it hangs is right after the first workbook in the folder is opened. I tried to get it to select A1 in the active sheet but that cell is not selected in either the macro workbook or the newly opened workbook.
Also, if there is an easy way to exclude my macro file (name contains ZZZZ) from the FileSearch............
Conditional Formatting With Dates?
Apr 18, 2014I am trying to do conditional formatting to make a cell say something for 3 different scenarios. If K6 > G6, then I need M6 to say "Early." If K6 < G6, then I need M6 to say "Late." If K6 = G6, then I need M6 to say "On Time."
View 7 Replies View RelatedConditional Formatting With Dates
Feb 15, 2014In my attached file,
In Column Q, Please drive a formula that shows.... after 7 days of column (M)reminder III these words must appear '' File Pending'' in 'COLUMN Q' and at the same, Indicator column (O) will also become RED.
Test.xlsx‎
Dates And Conditional Formatting.
Oct 3, 2008The scenario. Column A has a set (due) date all of the same. Column B is where review dates are typed in. Column C is where the status is, Due = still as yet to be done, but still yet to pass the Due date with no date in Column B (formatted yellow with the text "Due"). Complete = for when a date has been entered in Column B (formatted green with the text "Complete"). Urgent = the review date has passed and with no date in Column B (formatted red with the text "Urgent"). I have tried by editing previous similar formulas but to no avail
View 2 Replies View RelatedConditional Formatting Dates, Nth Value
Oct 24, 2008In A1 Downwards i have a list of dates for an entire year
I have highlighted these cells and wish to conditional format them.
I want every 3rd date from a specific date say in cell b1 to be highlighted up and down within the selected area. For example
____A _________________B
1 1/1/08 ____________ 5/1/08
2 2/1/08
3 3/1/08
4 4/1/08
5 5/1/08
6 6/1/08
7 7/1/08
8 8/1/08
9 9/1/08
Is there also a conditional format formula that i can use to highlight b1's day every month.
in this example highlight the 5th of every month again going up and down within the selected dates.
Conditional Formatting - Using Dates
May 28, 2009Im trying to get conditional formatting to look at a completion date in cell A5 and getting cell D5 to change colour depending on the length of time to cell A5. What I would like is for D5 to change to red if there is 4 months or less to the completion date in A5. D5 to change to orange if there is still 4 - 8 months from the completion date A5. D5 to change to green if there is still 8 - 12 months from the completion date A5. Hopefully the formula will also work if cell D6 is looking at completion date in A6 with the same traffic light system and so on.
View 3 Replies View RelatedCombined Dates Formatting
Feb 3, 2010I am trying to combine 2 dates into the 1 cell separated by the word "TO:" So I have: =B2&" TO: "&C2
Which results in: 40211 TO: 40240
What I need is: 02/02/10 TO: 03/03/10
Conditional Formatting For Dates
Oct 23, 2013date conditional formating issue
Valid to DatesConditional Formating
30.10.2013RED
2.01.2014RED
30.08.2014YELLOW
30.09.2013no formattingExpired
31.12.9999no formatting
The dates are in different format with dots in between as shown above Also with respect to date which has already expired, no formatting needed but i need a formula in the next column stating "Expired"
If valid to date is within 90 days from today- RED
If valid to date is within 365 days from today- YELLOW
Conditional Formatting For Dates?
Mar 2, 2014I have a sheet where K5:K26 contains names of months. I would like to conditionally format that column so that any month that is less than TODAY appears red. So, if that column has a cell value of February, the cell would appear red since we are already in March. I would guess this involves a formula using TODAY, but I can't figure it out myself.
I know how to apply conditional formatting; I just lack the proper formula.
Conditional Formatting And Dates
Oct 23, 2008I have a list of dates that I want to flag as red if the date passes and the task is not complete.
So I would have 2 columns
Estimated completion date & Completion date.
If the completion date cell is null AND the Estimated completion date has passed then the Estimated completion date turns red (or a red block appears in a new column if that is easier!).
How To Add Conditional Formatting To Dates
Feb 23, 2012I have a large spreadsheet with dates. I want blank cells to be conditionally formatted to turn red and cells with dates older than 1 year to turn yellow. I have cell C1 set up as the current date (Today()) and the others reference the other cells with dates.
View 6 Replies View RelatedFormatting Dates To Use VLookup
Nov 28, 2012I'm having trouble comparing two lists of clients with vlookup. The problem seems to be that the dates are in two separate formats. I'm combining the information to create unique identifiers (=A1&B1&C1).
example:
A1 B1 C1
Eric Reilly 01/04/85
comes out as: EricReilly01/04/85
A1 B1 C1
Eric Reilly 1/4/1985
comes out as: EricReilly31051
Conditional Formatting With Dates?
Nov 12, 2013Column C has dates in... and I have formatting that turns the cells in Column G Red if the date in Column C is 14 days or more old.
my problem is that if a cell in Column C does not have a date in it, the corresponding cell in Column G still gets formatted and I want it to not turn red if there is no date in Column C.
Conditional Formatting For Three Different Dates
Jul 11, 2014I am trying to use conditional formatting and its not working for me.
If I put the date 1 Jan 2014 in the cell of E2 I need a conditional format that will turn the cell yellow when it is two month from being a year old, so on 1 Nov 2014 it should turn yellow. Then I need it to turn orange when it is one month away from being a year old, so Dec 1 2014 it turns orange. And then when it is over a year old it turns red, So 1 Jan 2015 the cell turns red.
The formatting I have now turns them yellow, orange, and red but at the wrong times, not sure how to fix that.
Formatting Cells As Dates
May 21, 2008I want to enter dates in a column. How do I ensure that the user cannot enter an invalid date or entry that not a date such as text? Any invalid entries should automatically be Null.
View 9 Replies View RelatedConditional Formatting Between Dates
Jul 7, 2008We need to track the batteries if it is expired or not.Expiration is 3 months. once you received the battery, you need to put the date in the sheet (A1) and then the expiration will be automatically calculated in B1. another also, in B1, will change its color according to how may days left before the expiration date.
black color if the expiration is < 15 days
red color if the expiration is 45 days
white color if B1 is blank
Formatting Overdue Dates
Jul 4, 2006I have a spreadsheet where I am tracking tasks. I would like to conditionally format the "Completed" cell red whenever the Due Date has passed to easily identify which tasks are outstangin. And I would like that to happen no matther what date someone opens the spreadsheet. (I think I need to use TODAY() here.) Then once the user enters the actual completed date, the cell would go back to white with black font. Below is a sample:
Due Date Completed
5/8/2006 5/8/2006
5/8/2006 5/8/2006
5/12/2006
8/30/2006
This seems like it would be easy to figure out, but I've spent days on it and am not thinking clearly any more.
Conditionally Formatting Dates
Dec 12, 2006I have a workbook (attached) which has two sheets called “Team List” & “Masterfile Data Dump”.
TEAM LIST
This is the sheet that I want to populate when the data dump is a pasted into the sheet “Masterfile Data Dump”
MASTERFILE DATA DUMP
This sheet is where my data dump will be pasted to, columns A to Q and column R is a formula to work out the FTE
Calculation
The reason I want the list to auto populate is that it will change week on week and is a time consuming task keep updating it. I want to be able to type a Team Leaders Name into cell F2 and it populate the list of the agents in that persons team Cells F4:G18 (Name & Surname) along with there FTE (full time equivalent) in Cells I4:I18.
The problem I have is I have no idea how to calculate the FTE sum as the data dump can have one to five shift patterns (rows) for one agent (See my example on the “Master file Data Dump” Sheet of Joe Bloggs. /I have tried to use a VLOOKUP which I have used but this will only return the first value or first rows value of that agents name.
Conditional Formatting With Dates
Aug 7, 2007I want to be able to format a whole row with a particluar text colour when a date is entered into column A. I have managed this no problems using conditional formatting. The problem I am having is that I need to change that row's colour again if both colomn A's date is complete and a date is also entered into colomn B.
I have tried conditional formatting without success but I have the niggling feeling that I have read this it is not possible to use this method.
Conditional Formatting For Passing Specified Dates
Aug 24, 2009I'm having trouble putting the correct conditional formatting on a column of dates.
Basically, in column 'A' I have a list of appointment dates. Column 'B' contains breach dates for a 10week target. And Column 'C' contains breach dates for an 18 week target. Looking something like this:......
I need to format column 'A' so that the cells will turn Amber if the appointment was after the 10 wk breach date and turn Red if it was after the 18 wk breach date.
Conditional Formatting For Dates (x3 Criteria)
Jan 31, 2013I'm desperately trying to work out conditional formatting for different dates.
I have =TODAY() in F3
In the remainder of column F (from F19 to F625) I have either blank cells or varying dates (dating back to 2011 and dates going forward into 2014).
I need to Red/Amber/Green the following:
Green = any dates which are 45 days or more in the future from today's date
Amber = any dates which are between +1 and +45 days in the future from today's date
Red = any dates which are today's date or a date in the past
I can get one or two of the criteria working i.e. Green and Red, but not the Amber. Is one overriding or ignoring another?
Date Formatting - Comparing Dates
Jul 8, 2013I have a problem regarding month formatting
I have 12 columns, Jan 2013 - Dec 2013.
I need to set a format in which if I enter a num eg 15 on the Jan Column, format comes as "15-Jan" format
or If i type 20 on the march column , it comes as "20-Mar" format
I would need it in Date format, because I would be comparing dates later on.
Conditional Formatting For Expiry Dates?
Jul 30, 2013I have a training sheet where the training could expire in either 1 2 or 3 years, i am trying to get the conditional formatting for each to change color when the training is due to expire in 6 months 3 months and 1 month also to show red when expired
View 14 Replies View RelatedConditional Formatting With Dates And Blanks?
Oct 2, 2013I am currently setting up a project timeline, and at each stage i need the cell to change color depending on input. so for instance stage one happen -50 days. i work the date out need for completing and then the actual date is then put in the cell below which i want to go either
Green because it was done early or on time Amber if it has been done but was late Red if has not been done and the current date is now past the date of completion Stay Blank if it is Blank and their is still time to do it
for the green i am using a A2 <=A1
Amber i am using A2 > A1
for the red i'm using =And(A2<Today(),A1="")
the red one conditioning does not seem to be working and if their is nothing in the cell an its within the time its going green !