I'm currently running a macro which copies data from one sheet and pastes it to another.
One of the fields on the primary sheet is a date in the english format DD/MM/YY but when it pastes onto the second sheet it becomes a Julian date 39820 instead of 01/07/09, and when this figure is converted back to date format it becomes american format MM/DD/YY
Currently I've set the macro to PasteSpecial xlValue
I get sent a spreadsheet by one of our partners where the dates look like they are in European date format except thay are actually in American format.
example
01/09/2007 - they mean 9th January 2007 but Excel holds 39326 which is 1st Sept 2007.
I can obviously format them but when I sort the list Excel still reads 1st Sept instead of 9th Jan
There are over a thousand entries so re-typing isn't an option.
Got a random problem with the IsDate function and was wondering if anybody could help?
Below is the code I am using to check a user input of a date, and if it is a recognised date put it in to a standard format (dd/mm/yyyy). The problem is that VBA is checking for an American ...
I have US reporting package that e-mails a report, which I then run macros to put extract the required data, and pivot etc...
There can be anwhere between 1 and 15,000 lines of data.
The file is generated as a CSV file, with dates in the first column in the format mm/dd/yyyy. Other header information also has a "/" so it is impossible to do a text to columns.
I need to continue using UK dating format, so cannot change the Excel settings.
Can anyone provide me with VBA code to convert mm/dd/yyyy into dd-mmm-yyyy without it ignoring any dates where the dd is less than 12?
are there any easy way to reformat a fractional number: I need, for example, to make 5,23 look 5.23. Is there any function in Excel? I guess not, but have no clue where to start my VBA code.
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
I tried to copy a date from Temperature & Humidity Reading file using the formula ='[Temperature & Humidity Reading.xlsx]Sensor 7'!$C$2 to Summary file
if the date format is like this 6/10/2013 12:00:00 AM the result is fine, but when i try to copy that formula for the succeeding dates the results is same from what i copied.
I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.
The format of B5 looks like - 13/08/2014 16:39
The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".
The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14
I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.
I found this really great vb code for my spreadsheet to be updated with the current date whenever the range is updated. I modified it a bit to include a bit more in the cell A1. what I can't work out is how to format the date so that it only shows me the date as 06/07/2009. It currently returns the date and time.
I have a column with dates formatted as yyyy-mm-dd (2006-12-21). Then I have a cell with data validation set to a list based off that column, formatted the same way. The problem I run into is that, let say I select 2006-12-21, the value is returned as 12/21/2006. The reason this is a problem is later in my macro, I am using the value to open a file based off that date. I currently have the files named in the same format (2006-12-21.xls). So when the macro goes to open the file, it errors out saying it cannot find 12/21/2006.xls. I have no problem changing the name format for my dated files, however, as you are aware, slashes cannot exist in the file name.
So my question is, when selecting my yyyy-mm-dd formatted date, how to I get the macro to not read the value as mm/dd/yyyy? If it helps, here is the code I am using to retrieve the value:
startDate = Worksheets("Macro").Range("B5").Value B5 is obviously where the drop down is.
I tried to DL the HTML Maker to post my issue, but for some reason it wouldn't work with Excel2007. The problem is pretty simply, but I can't figure the code out. I have a column of data that looks like this:
Jan 19 @ 10:00am Jan 18 @ 7:21pm Jan 18 @ 7:15pm Jan 18 @ 5:48pm Jan 18 @ 5:44pm 000000061899 1 Jan 19 @ 9:01am Jan 17 @ 12:07am 000000039656 1 Jan 16 @ 5:42pm
000000087214 1 Jan 23 @ 4:07am
Basically month and day with no year. The months are Jan,Feb,Mar,Apr..and so forth. Some cells have no data, others have various numeric characters included (as above). I want a loop that will format to MM/DD/YYYY. I have another column with dates in that format which I can use Offset to pull the year if I need to.
I've been give a string of numbers (e.g. 2172003) and been asked to convert it into a date (converted to: 2/17/2003 ). Besides manually inserting the back slashes, what formula could I use to do it for me? (I've tried using the 'formatting cells' tool and it will not format properly).
I need to enter a range of dates and have any date that falls within that period (regardless of year) highlight. I currently have it referncing two different years, but I need more range than that and I can't get it to highlight dates in July if they were dates that happened 1, 2, 3, etc. years ago.
I am wondering if I am able to display dates as text. Example: If I have a date that displays as 5/6/2014 is there anyway I can get it to display as 562014 as text?
Is there a way to date format a cell so that when I enter only the number date (ie today is October 13 so I'd enter 13) the cell formats to the current month with the date?
So for today I'd enter "13" and the cell would read 13-Oct. Additionally, when the month turns to November that same cell, without me making any changes to it, would change to 13-Nov. For future dates it would always show the current month with the date I entered manually.
i seem to be having a problem formatting the date in my table. I format the cells in the 'date' category to display the date as '14-Mar-01' but only some of the dates in the cells are changed. I have entered all the dates in this format: 01/01/09. Why do some cells change to the correct format and others do not.
I have set up a spreadsheet with a set of dates occurring in one column.What I would like is for the cell to highlight in yellow if the value is 3-6 months ago more from today's date and then highlight in red if the value is 6 months or more from today's date.I'm using office 2013 if that makes a difference
The data in cell E10 is a date derived from the formula
=IF($C$2<>"",IF(E10="",NOW(),E10),"")
It is a time stamp and works fine. My issue is when I copy and paste the data I am getting the date and time. I just need the date copied not the time. I currently just go in to the date and delete the time from the entry but need to refine the process. Cell E10 is the only issue in the current setup.
I have a userform that has a date entry in it, the date can be entered in two ways either by entering the date into a textbox or via a popup calendar and the selection is then placed into the textbox, the problem I have is that if any date say 1st october 2009 is selected as a DD/MM/YYYY format the data sheet that it is going into is formatting it as MM/DD/YYYY, but it doesn't happen if the day is over 12, so the 13th october would show as 13/10/2009.
Once in a while, for some reason, Excel decides to format a whole random file in my hard drive to dates.
So far, I haven't bothered because it only happens once each month and a half or something like that.
The thing is, recently, it happened again. But now, each time I import some data (copy/paste) in a sheet, Excel decides to format any number data to dates. The data imported has nothing to do with a date (1-5 or 1/5), it's just numbers.
I originally thought it was the imported data that was at fault, but it's not.
I can't format my cells to text, because it's going to screw up a lot of my formulas and it wouldn't make sense anyway.
I have a macro that pulls the date from cell C9. The formula I have in C9 is =today(). In the cell it shows todays date the way I have the cell formatted. MMM-YY. Which is The formula bar is displaying the formula in some and the date in the form of mm/dd/yyyy.
I need the formula mmm-yy to be in the cell and have the cell and the formula bar display Nov-13 in this case. The reason being that the macro is reading from the formula bar, usless I can change that some how?
I have a column contain cell with value of dates. The value is from Vlookup formula. The problem is when I try to do color code to yellow for any past date from current date to all cells that contain the date value in that column, I dont see any change.
The formula I used for past date from current is =today()-1 (so that should be correct). So, I am wondering why none of the dates that past do not change the color thru the conditional formatting I did.
The Conditional formatting formula I used:
"Format only cells that contain" -- > Cell Value -- > less than or equal to -- > =today()-1