Formatting Cells To Date - Best Practice
Aug 7, 2006
I thought I read somewhere that it wasn't good practice to format a whole column(s) b/c then you would have empty cells that were formatted. Is there a better way that you guys format your columns without selecting the whole column? I know you can do this in VBA, but I was just wondering if there was a way that I'm unaware of.
Second, is there a formula that can inserted into conditional formatting, that will check a named range against two other cell values. If that ranges date is > AB2 and <AC2 then it will bold that range? I'm trying to figure out how to do things without always relying on VBA if possible.
View 2 Replies
ADVERTISEMENT
Sep 16, 2013
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
View 2 Replies
View Related
Jan 17, 2013
I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.
conditional formatting.xlsx
View 4 Replies
View Related
Oct 13, 2008
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.
View 3 Replies
View Related
Jul 31, 2012
I feel like this is a pretty simple question to answer but I cant find an answer to it anywhere or I'm just not skilled enough to understand it. I have two columns D and E that have dates in them. Column D is the purchase date of a computer and column E is how many years months and days its been since it was purchased. I want both columns to be the same color. If the computer is 2 years old or less I want it green, 3-4 yrs old yellow and 4+ red. I have it working for column D but I can't figure out how to apply it to column E.
View 7 Replies
View Related
Feb 24, 2010
Does anyone know where I can get a large amount of generic data that I could import? I don't want my skills to get rusty while I am out of work. I keep finding little tables here and there, but I am looking for a big file that I can really play with. Maybe something with 20 or 30 columns and several hundred rows?
View 9 Replies
View Related
May 7, 2009
I am trying to link a range of cells from one sheet that contain various dates to another sheet in the same workbook. Some of the cells are empty. I select the source cells (say, F8-F19), copy, select target worksheet, Paste Special (and select Paste Link button). Target cells display accurate date information that updates as source cells are changed, however, any source cells that were blank now read 1-Jan-04. I have checked to ensure that all source/target cells have the same formatting (Number-Date).
When trying to Paste Special values and/or number formats only, the option to Paste Link is no longer available. How can I link the cells so that blank source cells result in blank target cells? Issue seems similar to: http://www.excelforum.com/excel-gene...worksheet.html
View 3 Replies
View Related
Sep 6, 2013
I have a date in a cell that when it is less than today() (so yesterday's date) would turn red and the 5 cells to its immediate right would also turn red, if the date is still current then it would be green and so would the right hand cells.
View 4 Replies
View Related
Oct 16, 2013
how the scrolling date bar in the attached is achieved. I am not asking for a step by step with code I am just after the basics of how this was achieved and i will research the rest. Most importantly is how to associate the dates with the rest of the cells in the column, as I imagine this is how I am going to fill the cell...The purpose is to add this scrolling date bar to my workbook, also attached.
View 2 Replies
View Related
Sep 6, 2008
I looked through the format link: Conditional formatting page on Ozgrid and was unable to figure out what I want to do.
What I want to do is check a range of cells which have dates in them, and then if they are between such and such dates, they will turn a certain background color. But by "such and such" I mean, between the dates entered in two different cells.
So in the range of A1:A10, if any of those cells are between b1 and b2 then apply color1. If any of them are between b2+1 and b3, then apply color2.
If it's not possible tell me but I think it should be as I can do it with conditional formatting. I need to have 16 conditions though. What it is is a chart with projects in rows and dates in columns. The dates often change and I want to rearrange the entire schedule visually just by changing the date in another place. (I change the date in the target cell, what I call b1 above, and b2-10 are calculated based on that date). Hope that's enough information.
View 9 Replies
View Related
Nov 30, 2007
I have a number in A1, another number in B1, a Date in C1. In D1, I am referencing all 3 of the other cells: =CellA1 & "-" & CellB1 & "----" & CellC1. Trouble is the date is coming through as a number. Is there a way that I can get the date to show as displayed as opposed to the value of the cell?
View 2 Replies
View Related
Dec 2, 2008
Am using a number of checks on Worksheet_Change and Worksheet_Calculate events and, depending on the result of the change, resetting a cell value.
For each of these I am using
Application.EnableEvents = False
Range("A1").Value = 0
Application.EnableEvents = True
to avoid that the change of A1's value triggers a calculation event and gets me into a loop.
However, something in my project is setting EnableEvents to False and not resetting it. I've searched the entire project for all instances and each "=False" is swiftly followed by a "=True". The code is not causing an error
The only way to get around this is to have a user press a button to re-enable events, but I can't tell them to press the button because there are no events to trigger the message!
View 9 Replies
View Related
May 13, 2006
I have yet another general design or best practice question regarding linking data between sheets in a workbook.
So the example scenario is we have 12 individual customer pages shown amounts invoiced and credited for the financial year.
I want to create an overall summary page.
Is it best to:
1. Use straight link eg. =Data!A1
2. Use Vlookup
3. Use Indirect with Address function
4. Use Database functions
I believe providing you have enough available memory that there isn't an issue with any of the above methods.
View 3 Replies
View Related
Jun 28, 2006
Task - Streamline & Automate daily Excel Tasks for 13 Staff & Manager, based in 2 different locations. Current Status. 25 workbooks, "Shared", + 1 Workbook {List.xls}, "Not Shared". All Validation lists are in this List book. 1 main data entry sheet in each workbook, feeding totals on Main Tab. Each data entry sheet is 10 columns, 250-700 rows. 5 Colums are free text entry, 5 are fed from data validation lists. The data validation lists are on another Tab, which is linked to a non shared workbook.
Each workbook has a Main Tab, which totals data input, and represents data in colours, in different cells..Picture a warehouse, with shelves & racks. I am in the middle of designing a simple form...but not so simple..thats another story. I am concerned that there is a lot of potential for disater, especially with shared books. I have thought of the access route, but the staff need to see colours on the sheets. Any opinions on what the Best Practice may be to handle this scenario. My main forte has been formula, pivot tables & external data..this is a new challenge
View 3 Replies
View Related
Aug 10, 2007
I have been tasked with performing a work measurement study to improve productivity in our organisation. I managed to adapt a paper based report to an excel file; with the view of creating a model. I also intend adding the cost of employing people in specific positions to determine the cost per hour for each specific activity.
View 2 Replies
View Related
Aug 11, 2008
I am trying to reorganizing my database. Therefore data i like to restructure my current data (sheet 1) into a similar format of sheet 2. Is there a way to automate this process?
View 3 Replies
View Related
Feb 10, 2009
I need for coloring "Expected Delivery Date" column corresponding with "Control Date" and "Control Result. But I want to correspond with the cells' last content of "Control Date" and "Control Result" But first of all I want "Control Date" should be flashed 10 days ago of it's date... (it's already done on my excel file)
Then
I want "Expected Delivery Date" as green when
- "Expected Delivery Date" =< "Control Date" and "Control Result" =Y (Means Control is Ok on due time)
I want "Expected Delivery Date" as red when
- "Expected Delivery Date" > "Control Date" and "Control Result" =Y (Means control is ok not on due time)
- "Expected Delivery Date" > "Control Date" and "Control Result" =N (Means control ok is not given, delayed...)
View 3 Replies
View Related
May 3, 2014
How can I condition format a cell to show no format if the cell contains a date
Please see the attached book1.xlsx for more info. Book1.xlsx
View 7 Replies
View Related
Jul 22, 2008
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
View 14 Replies
View Related
Mar 12, 2014
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx
View 3 Replies
View Related
Oct 19, 2006
I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:
Formula is = MOD(ROW(),2)
My Second Condition is
Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
View 7 Replies
View Related
Oct 31, 2008
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
View 9 Replies
View Related
Jun 17, 2013
I 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.
View 4 Replies
View Related
Aug 15, 2014
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] ......
View 3 Replies
View Related
Nov 17, 2011
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
View 5 Replies
View Related
Jul 18, 2006
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??
View 5 Replies
View Related
Jul 2, 2014
So i've been trying to do this invoice/inventory/client , I couldn't find a way to somehow register the whole invoice as it is, so i can later print/visualize it if i lost the copy or something.
It would be best if it just stayed stored in a variable and not always visible, and only appeared if i wanted to check/print it.
The whole invoice would be from A1 to F30
Excel 2010 btw
View 5 Replies
View Related
May 3, 2013
What I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
View 5 Replies
View Related
Sep 25, 2009
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
View 3 Replies
View Related
Feb 11, 2010
i m having problems with a simple formula but i cant get it right
in cell a1
View 3 Replies
View Related
