I have used excel for years. I understand how to format dates. However, lately, after I tell the cell to format it as a date, and then type in a date, it inserts some random date (not the numbers at all that I entered).
View 8 RepliesI'm trying to format a singe cell based on if a row of dates is aged more than 3 days.
Column C is for Load Date. Rows C2:C100 may or maynot have a date in them. Cell C101 is a count (=counta(C2:C100) of how many cells above contain data.
I'm wanting to format C101 to turn red if ANY of the dates in C2:C100 are older than 3 days from the current day.
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
I have a some dates in a format which are not being converted into the normal date format. So i made this formula to convert it into the normal date format:
=IF(LEN(B218)=10,(MID(B218,4,2)&"/"&LEFT(B218,2)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=9,(MID(B218,3,2)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=8,(MID(B218,3,1)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,"")))
Formula is working fine except on this type of date "9/9/2013"..i also tried an OR formula with mid but didnt get the desired result.These are the type of dates:
12/10/2013
12/9/2013
9/11/2013
9/9/2013
7/25/2013
(Date1-Date2) : what's the format I should use to obtain the result in number of days.
For example :
(23/12/2006 16:30:00) - (18/11/2006 15:30:00) : It should give 35 days 1 Hour (35:01:00:00 - dd:hh:mm:ss)
How can I have this format (dd:hh:mm:ss)
I have row 1 contain dates as:
[Code] ......
and so on ..
And I want all dates that is saturday and sundays marked in grey. If its possible I want the whole D column marked grey (if D1 is a saturday or sunday), is it possible?
I have a file from a database that includes birthdays and anniversaries. These are mainly in US format as far as I can tell, but I am trying to get these all in UK date format but when I try and change any of the formatting half of them don't seem to change.
I have attached the file : Dates.xls
I am trying to move the data from sheet1 column A to sheet2 column A while reformatting the data from its current format (dd/mm/yyyy) to format (mm/dd/yyyy). The data from sheet1 is provided to me by a client and is produced daily through their system which they have told me they cannot change the formatting on the reports. When i try to move data from sheet1 to sheet2 and reformat it only half the data formats into the correct date format. does anyone know how to get the data from sheet1 to format and move to sheet2 properly?
View 3 Replies View RelatedI have attached a sample of the raw output of some call records from our telephone switch. My problem with the date data is twofold; first of all it outputs it in mm/dd/yyyy format, or so it appears. But when I try to change the format to mmm/ddd/yy Excel always interprets the 2nd pair of digits as the month instead of the first two. For example,the following 2 calls are from Jan 1st and Jan 2nd respectively;
01/01/2010
01/02/2010
but excel always treats the 2nd record as Feb 1st. I have read numerous posts from people with similar problems but I can't get any of their solutions to work with my data.
The second problem is that on the 13 of each month the phone switch suddenly stops putting a leading zero in front of the date making the data totally unreadable to Excel apparently. Again, I read a post of a similar problem but can't get their solution to work. My ultimate goal is to reformat the date data so that it is displayed as; ddd mm/dd and leave out the year entirely. Ex. Fri 02/12 as in today...Friday, Feb 12th. I really don't know if the problem is with the data or me at this point.
I am also new to vba (am teaching myself with the help of the guru's on this site!). My problem is to do with dates. I have created a variable in VBA that reads a cell with the value of a date in text format, for example SEP2006. I want to use this value to paste into another excel cell. When I do it actually creates it as a numeric date when I really want to just use it as text, "SEP2006". I have been looking at this for a while and I'm not sure if it's a case of "can't see the wood for the trees". A small example of my code is:
Sub DateValue()
Dim dateValue As String
Range("D2").Select
datevalue=activecell.text
Range("a3", [b65536].End(xlUp).offset(0,1))= datevalue
End Sub
I'm in the process of setting up an Excel document and I need to be able to have it display dates in a specific format. I need it to express just a month and year such that the month is represented by a letter (A thru L) and the year is expressed as its last two digits such as in the these examples:
May 2012 = E12
Mar 2009 = C09
Nov 2011 = K11 etc.
I want the date to come out in this format regardless of how the user enters it.
Below is a format of dates I want to make it in weekly format, select the Fridays or any specific day and separate them from this list.....
01-10-2013
03-10-2013
04-10-2013
07-10-2013
08-10-2013
[Code]....
I have dates (column E) and times (column F) currently in the format mm/dd/yyyy and hh:mm .
However, my formula in excel deals with dates and times in the format mmddyyyy and hhmm. How do I change the values of the dates to exclude the "/" and the times to exclude the ":" operators?
I want to change the actual value of the cell too. Basically, using custom format mmddyyyy in the date column will not work (because the true value of the date will still yield mm/dd/yyyy.
I need to format dates on the data labels of my pivot chart. My table has the correct format: dd mmm yy but the chart is showing dd/mm/yyyy.
How do I change this?
I have a spreadsheet with due dates in column X. I want to create conditional formatting so that cells turn red when the due date is less than or equal to 30 days away, but turn gray when the due date has passed.
I've accomplished the first step by using the following: =AND($X6
I want to display 2 dates in one cell and have them custom formatted.
Eg. Display in one cell: 26 Jun - 27 Jun
With text typed: 26-6 - 27-6
(Or something along the lines of)
I know that dd mmm will display one date. But I cannot get 2 dates formatted. I tried dd mmm; dd mmm" - "dd mmm, which didn't work.
I have hit an issue when tranfering a string date from VBA to excel, excel thinks the string is US format and swaps around the days and months even though the system and excel are setup for UK date format:
Simple example of the problem:
Sub test()
Dim testy As String
testy = Now
'testy will equal 04/10/2007 17:20
Range("A1").Value = testy
'A1 will equal 10/04/2007 17:20
End Sub
Now I know in this example that if I used DateValue(testy) it would work, but our situation is a bit more complex as we are running this is a loop testy is not always a date.
I am trying to use Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">Conditional Formatting to change the cell color based on wether the content is an even number or an odd number. Actually it is an even or odd date but I figure that it will read the date as it's serial number and that would be a number. I thought I would use something like "Formula is: =MOD(VALUE,2)" but I can't get it to work. I'd like to be able to do this without using the Analysis Toolpack. Even though ISEVEN seems like it might be a good solution.
View 7 Replies View RelatedI 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!
I have a sheet with data imported of another software. In the column "G" are all the dates, and the problem is how can i change the format, because, some of them are "mm/dd/yyyy" and the rest are "mm/dd/yyyy".
Is there a way to change it to the same format? maybe there are some dates like "11/02/2007" that are "02/11/2007" (i don't know how change it with code), and others that the difference between formats is logically clear. I want to get the format "dd/mm/yyyy".
After this i want to apply a filter between two dates, for example, (in dd/mm/yyyy) from01/05/2007 to 30/08/2007.
I've tried different ways, but i'm not able to transform the format of the dates to the same format, although i've read your help and other posts that talk about this.
I am using a CSV that has the date in Column A in the format of DD/MM/YYYY. When I open this CSV using a VBA comand the Format of the Cells in this col are then formated as either Date or General. The general format happends when the date cant be turned into US format and when it can then the date is turned US and displayd as such.
SO
14- Sep = 14/09/2007 as General
10- Sep = 09/10/2007 as Date
Im in Australia so the correct format is DD/MM/YYYY. Microsoft has issued a fix for this using Local:=True in the file open comand which is all good and well as it works unless you have office 2000 when it crashes. What would be the best way of fixing this, so that the Dates are all correct or the col is all the same format?
Using Excel 2007. Would like to format X-Axis values to display 1st month and 13th month as Feb 2, '08 for example. I would like the other 11 tick marks to remain. Sample attached ( Saved in 2003 format, I .xlsx was invalid file type)
4th micro chart uses F1:F13 for values for x axis
I have a range that contains dates, with some cells within containing text (e.g. "TBC" or "planning will occur wk42"). I have basic rules as below:
- highlight red if older than today
- highlight green if today or in the future
The problem I have is that the cells that have text are highlighted green which I don't want. I also don't want to have to go through the range removing conditional formatting from each of those cells each time I copy over new data. Is there a way of telling excel to not use conditional formatting on those cells that contain text so I can manually highlight as I need? Some have text as that is how I receive the data.
Attached spreadsheet has the resource names and their date of joinings (Column - F2), now I would like group the date of joinings into Month & Year format. I tried text formula and then converted it back to date format but its giving incorrect results. For ex - Column F2 has 9th Sep -2013, whereas my formula in H2 is showing Sep-2014. Any way to group these dates into months in a simpler way?
Once I have this grouping done, I would like to do a pivot and sort them.
I'm working with a big dataset and the dates come out in this way:
94WAO7t.png
EDIT: Example of book included
When I import data , the dates appear as follows in Col G sheets "Imported Data"
If I select Col G and use Text to columns and select mdy, it gives me the dates in the correct format. However, If I use the macro recorder to do this and then run the macro, it does not work
I have attached the dates after using text to Columns wjhich is the correct format.
Imported Assets *G207/01/2006302/01/2007402/01/2007511/01/2008601/01/1985720/11/1990801/01/1985901/01/19851001/01/19851101/01/19851225/09/19921302/01/1993
Imported Assets G201/07/2006301/02/2007401/02/2007501/11/2008601/01/1985720/11/1990801/01/1985901/01/19851001/01/19851101/01/19851225/09/19921301/02/19931401/05/19931501/06/19931601/08/19931701/01/19941801/09/1995
Is there a way I can convert dates to text in a CSV file (opened via excel) using a macro?
Currently I have excel recognised dates in the first column of the format dd-mm-yyyy and a corresponding values in the second columns.
I know once I have the file open I can enter the following formula in an adjacent coloum "=text (A2, "MMM-YYYY") but I need to change the format in the existing date feild and not create a new coloum. Sure I could copy and paste the new formatted dates into the cell, but in terms of what I need to do, this is not practical as I several of these CSV files that I need to query and extract data from daily using a macro.
The macro is not currently working because the date format is wrong. I need it to appear as MMM - YYYY. When you view the date in excel it must appear like this (as it would appear in say microsoft word) and not with the underlying date format.
Is there a way to find cells that have a date in them that has a text digit with a 2 digit year to 'xx07' rather than having to click on the error and selecting it manually.
it's playing havoc when I'm pulling some data from another workbook so converting en masse would sort the problem out!
Anyone know how to do this?
I have dates imported from an external application in the format:
71205
for december 5th 2007, for example