I would like to know if it is possible to format a cell where you enter a date (08-02-2009) and it returns "08-02-09 thru 08-08-09" that weeks date range in that cell.
Format custom mm-dd-yy " Thru " mm-dd+6-yy
I know will not work but is there a way to do this??
I would need support for the following:
I have one column with a date 26.09.2014 and one with a time: 09:15
I would need to merge both cells to be displayed as per this format: 26/09/2014 09:15
I use this formula: =CONCATENATE(A1;" ";B2) and get this result: 41908 09:15
I've tried to change the date format in first cell but nothing works and I always get the ref. number of the date instead.
What i need is the following: In cell B1: if A1 is greater than 08:00am but less than 14:00pm than B1 should have a tick which is green, if cell A1 is greater than 14:00pm than B1 should have a cross which is red...
View 7 Replies View RelatedI dont know the correct terminology with whick to phrase my question, but I would like to know if its possible to copy say columns A-J on "worksheet A", and copy columns A-J on "worksheet B", each of which have different column widths, and paste them both to "worksheet C"?
My problem is columns A-J on "worksheet A" are perfect and copy and past fine to "worksheet C". But when I copy columns A-J from "worksheet B",which have different widths, and paste them to "worksheet C" right below what I had previously pasted from "worksheet A", the column widths interfere with each other.
I have tried a special paste, and it seems to re format everything above the current page its pasting?
I have created a multiple choice quiz maker that randomizes the questions and responses. This means that the quiz questions/responses must be copied and pasted each time a new quiz is generated. But in doing so, Excel loses formatting (such as super and sub scripting) and some special characters (like pi, alpha, the degree symbol).
Is there a way to get such things to copy properly from cell to cell, sheet to sheet, using Excel?
I would like to use the following line to calculate the formula,
In the format i have already specified.
But to leave the values in the cells apposed to the formula.
This way when i delete the source sheets the results will still be left as values not formulas.
It can probably be done in 3 lines but i have to do this for 9 different rows so was wondering if there is a way of combining the three tasks into one row
i have 2 listboxes with numbers if user has selected choice in both then i want to enable okbutton. i tried in okbutton's mousemove: if listbox1.selected = true AND listbox2.selected = true then okbutton.enabled=true.
View 3 Replies View RelatedI have a workbook which contains lots of formula and is therefore very large (about 70 mb). The sheet pulls information from field instruments every hour of every day for the entire year.
I want to write a macro, that loops through a range (B5:B8764) and copies the entire row if the value in column B is less than =NOW() - 7 days. The copied rows should then be pasted with values only exactly where they were located e.g. row 5 should be pasted back to row 5 the only difference being only the values will be pasted.
Using 26th January 2007 as an example
I have formatted a cell to show this as dd 26.
When I copy and paste it to another workbork where I needed it shown as a number it appears as 39108.
Is there an easy way to convert the Convert Date Format?
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.
dust 1.xlsx
I have a column of numbers, in the next column I have a formula =left(a1,len(a1)-2) which chops off the last 2 digits of all in column A. when I copy the values from column B and paste special them as values they come out with an error "Convert to number". converting 20,000 cells can take some time. Is there a way that I can paste them to automactcally be numbers?
View 3 Replies View RelatedI have to excel files
1. Temperature & Humidity Reading
2. Summary
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.
[Code] ......
In cell c23 i am trying to return a date which is in cell h4 and then the word "to" and then another date in cell l4
the formula i have is
i m having problems with a simple formula but i cant get it right
in cell a1
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.
View 2 Replies View RelatedI 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).
View 9 Replies View RelatedI have a very large spreadsheet that looks like this
wew.png
What I would like to highlight those cells in green that got to their current state before their due date.
So the date in F is before the Date in H and highlight those in red that did not get to their date in F before the date in H..
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.
View 5 Replies View RelatedI 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?
View 6 Replies View RelatedIs 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.
im having trouble getting this date to format in a sentence
View 2 Replies View Relatedi 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.
View 5 Replies View RelatedCan you have a conditional format that formats the cell one way before a certain date and then another way on and after that date?
View 7 Replies View RelatedI 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
View 4 Replies View RelatedI will like colour of the cell (column M) to change if the SOS (column M) date is in 7 days and their is no scope column D.
View 1 Replies View RelatedI need to adjust the attached code as follows;
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.
Conditional Formatting for Due Dates. I have A1 which is due date and I want A1 to be formatted with red text if it is 2 days before the due date.
View 11 Replies View Related