Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Formatting Cells To Date - Best Practice

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Date Formatting Cells
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 Replies!   View Related
Conditional Formatting Of Cells Between Date Range
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 Replies!   View Related
Linking A Range Of Cells - Date Formatting
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:

View Replies!   View Related
Join Cells & Retain Date Formatting
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 Replies!   View Related
Conditional Formatting For Cells Which Have Multiple Date Info
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)

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 Replies!   View Related
Conditional Formatting Date Cells Based On Entry In Adjacent Cell
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 Replies!   View Related
Conditional Formatting Conflict: Change The Cells Font Color And Bolding As Long As The Value Is Within That Date Range
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 Replies!   View Related
Practice Data
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 Replies!   View Related
Application.EnableEvents Best Practice
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 Replies!   View Related
Linking Data - Best Practice
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 Replies!   View Related
Best Practice - Multiple Workbooks?
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 Replies!   View Related
Best Practice Worksheet Struture
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 Replies!   View Related
Best Practice Spreadsheet Design
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 Replies!   View Related
Date Cells In Project Plan To Change Based On Other Cells Including Other Date Cells
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 Replies!   View Related
Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells
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 Replies!   View Related
Formatting A Date Value
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.

View Replies!   View Related
VBA Date Formatting
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.

View Replies!   View Related
Date Formatting?
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 Replies!   View Related
Date Formatting
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 Replies!   View Related
Date Formatting...
i m having problems with a simple formula but i cant get it right
in cell a1

View Replies!   View Related
Formatting Date In VB..?
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 Replies!   View Related
Formatting Cells Dependent On Other Cells Relation To That Cell.
i have just read the thread started by scabertrain regarding "if" conditonal formating, but mines slightly different and i cant make head nor tale of the fomula shown.

So... Monthly budgeting... using a projected outgoing (A1) and an actual outgoing (A2) i want the colour "A2" to change depending on whether i have spent over the budgeted amount, green being underspent, orange for on target and red for over spending....


I predicted (A1) that i was going to spend £50 on a night out, but my actual spending (A2) was £100, i would like A2 to turn to red.

View Replies!   View Related
Time & Date Formatting
i have a cell with a date in it, e.g. 18/09/09 is there anyway that i can ask excel 2007 to look at the cell and simply return the month into an adjacent cell, so in this case September.
A1 B1
Date Month
18/09/09 September

View Replies!   View Related
Conditional Formatting On Due Date
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 Replies!   View Related
Special Date Formatting
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??

View Replies!   View Related
American Date Formatting
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

How can I make it paste into English format?

I'm using excel 2003

View Replies!   View Related
Conditional Formatting For Date
I have 2 columns that I need to set conditional formatting for. In one column (D) I have a date of when a person needs to be paid. In the next column (E) I have when the date of payment was or nothing indicating I have still not paid this person. What I need column D to do is have conditional formatting (im assuming this is the right way to do this) color the cell if we are late or if the person has not been paid yet according to the date in the E cells. Here is what I have so far:

(in cell D1)
Condition 1:
Cell Value is greater then =$E1
Color grey

Condition 2:
Cell value is less than =$E1
Color pink

He is where im running into the trouble. Condition 1 indicates that if cell D is greater then cell E then I have paid the person on time or sooner (grey). Condition 2 shows that if cell D is a date before the date in cell E then it turns pink indicating I was late on payment.

What I also need the condition to do is change cell D pink if there is no info in cell E, which is indicating that I still need to pay someone. So I tried a 3rd condition, but it doesn’t coincide with condition 1. I tried:

Condition 3:
Cell value is greater then 0
Color pink

What I need the 3rd condition to do is say if cell E has nothing in it then I need cell D to change to pink. Im assuming I need 3 conditions for this to work, but im not to sure.

View Replies!   View Related
Conditional Formatting Before And After Certain Date
Can 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 Replies!   View Related
Date Formatting: Getting In Sentence
im having trouble getting this date to format in a sentence

View Replies!   View Related
Number Formatting To A Date
Is there any way to turn a number that is in general/text format into a date?



to read:

8/2005 or Aug'05
9/2005 or Sep'05
10/2005 or Oct'05

I have tried using IF statements and it looks like there's a limit in the amount of scenerios you can have. I get data in text format that comes to me in numbers and I'm just looking to turn those numbers into dates so I can sort them properly.

View Replies!   View Related
Formatting Date In Table
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.

View Replies!   View Related
Formatting Date With Pop Up Calendar
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.

View Replies!   View Related
ListBox Date Formatting
the following code populates a 9 column listbox in a userform. Me.ListBox1.List = Sheets("temp"). Range("A1"). CurrentRegion.Value.

The first column of the listbox contains a date and this is where my problem lies. The date formatting from the "temp" sheet is not being maintained when the listbox is populated. Can I run a loop or something to scan down the first column of the listbox and if the entry is numeric convert it to the date format "dd-mmm-yyyy", at the moment it seems to be using the date format "mm-dd-yyyy". Also, is there a way to autofit the columns of the listbox?

View Replies!   View Related
Conditional Formatting Based On Date
I am trying to use conditional formatting to highlight items that are either past due or coming due soon. The data to be evualuated is in a report that I extract from another system and run bi-weekly or monthly. The dates included in the report represent the date in which something was last updated, certified, tested, etc. All dates expire one-year from when they were last completed. The colors that I want are as follows:

If something is past due (date shown is more than 1 year old) or due within 30 days = RED.

If something is due within 31-60 days = YELLOW

If something is due within 61-90 days = GREEN

So, if an item on a report is showing it was "LAST UPDATED" on 2/21/2007 it should show up RED because it is due within the next 30 days.

If an item is not expired (less than 1-year old) and falls outside of the defined 90 day window then it should just remain as is with no formatting.

View Replies!   View Related
Formula For Static Date Formatting?
Here is what I currently have to make the cell blank and highlighted yellow in the event the date in K1 equals the second day of any month


Conditional formatting is just =DAY(K$1)=2

What I need to do is apply these same conditions to the cell if the date in K1 is equal to 02/10/10 or 04/10/10 or 06/10/10 or 08/10/10 or 10/10/10 or 12/10/10

View Replies!   View Related
Formatting: Date Imported From Oracle
I'm generating a report on an oracle based software which then I export to Excel. Unfortunately the dates come out very messy. I've included an .xls file with the dates. They are untouched. As you can see, some are left aligned some right aligned. Nevertheless, all dates are of the format dd/mm/yyyy with zeros (0) automatically omitted - this is how they're shown on the Oracle report, I've changed nothing.

I want all the dates on column B to have the same format as the date on cell D3. Of course, the format must remain dd/mm/yyyy.

Please feel free to ask me questions if any of this is confusing.

View Replies!   View Related
Date Formatting Copy/paste
I have a spreadsheet of donations and the dates on which they were made. I input the dates like so: 1/2/2009, and then I format the cells so they appear like this: January 2, 2009. When I use this date in for MS Word "mail merge" functions, the date shows up in its unformatted formula bar form: 1/2/2009. I would prefer that it show up like January 2, 2009.

My question is this: is there any way I can change the cell formula bar to read like the formatted version rather than the non-formatted version? I want the formula bar to read "January 2, 2009" instead of "1/2/2009". Obviously I could type each date manually into the formula bar, but I'm looking for a quick copy/paste solution since there are hundreds of cells. I've messed around with "paste special" options to no avail.

View Replies!   View Related
VBA To Change Cell Date Formatting
I got a calendar macro running, the thing is that when the date is selected (and it gets entered into the cell connected to the macro), it needs to format it according to whether the date selected is today's date or not.

If the date selected in the calendar is today's - then it needs to enter dd/mm/yyyy - h:mm AM/PM, etc...

If it is it not today's date, then I only need dd/mm/yyyy to come up on the output cell.

View Replies!   View Related
Question About Formatting A Cell With A Date
I have a workbook with 8 sheets within it. I want to be able to change the date on the 1st sheet and have the other 7 just automatically change so I don't have to go to each one. I know it can be done, I just can't get it working.

View Replies!   View Related
Date Function (conditional Formatting)
I would like a date in a cell to flag up in a colour (say, red) once the date has expired by a certain period (e.g 7 days). How can I do this (conditional format).

View Replies!   View Related
Replace Method Changes Date Formatting
I simply want to find and replace a character within many cells that have the format general using VBA code. The cells are all dates and are seperated by a '.' but i want them to be seperated by a '/'. I have used this code below to do so, it changes the '.' to a '/' fine but it swaps the month with the day e.g. '08.09.2006' would become '09/08/2006'.

Cells.replace What:=".", Replacement:="/", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

I have noticed once the code is run it changes the format of the cells to date, even tho i include the 'ReplaceFormat:=False' function.

View Replies!   View Related
Formula + Conditional Formatting For No Of Days Before Due Date
Heres what I am trying to do:

I have a due date of 30/10/2007 in cell A2, I would like the cells in row 2 to change to the colour green 15 days before the due date (15/10/2007) and then I would like Row 2 to change to the colour Amber 10 days before the due date (20/10/2007).

This is what I was trying to do but it is not working for me:

1___Due Date________15__________10

The Formula in B2 and C2 work fine.

I was then thinking that I could put a formula in Conditional formatting that says something like
If todays date is equal to b2 then Green else do nothing or If todays date is equal to c2 then Amber else do nothing

I can't figure out the formula for that though.

View Replies!   View Related
Custom Formatting A Date In A Text Entry
I have a date in H34, 21/03/2009.

In I34 I have the same date again, except formatted as MMMM YYYY so its showing "March 2009".

In G10, I want the cell to read: Number at end of March 2009, with the month and year being taken from the date range. To be honest, I can get the year okay using the Year() function but cant get the month as cant convert it back from a numeric.

View Replies!   View Related
Conditional Formatting Relating To The Current Date
I am using Excel 2002. I have a spreadsheet with a list of dates. How can I get conditional formatting to highlight the cell, if the date listed is before the current date.

View Replies!   View Related
Date Formatting In VBA :: Dates That Are Imported As Text
to change the format of some dates that are imported as text.

In a previous request ...


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...


And here's the recorded macro, deliberately left exactly as is

View Replies!   View Related
Date Formatting: Show Just Year As When I Go To Format
I have the following format in Excel, how I change to show just year as when I go to format, cells, it appears to be customed to be h:mm:ss but I need it as yyyy, which if I change to it shows 1900..


View Replies!   View Related
Formatting Date: Presented In The Format 20091012
I am generating a report from an accounting software every month and what I get is an excel spreadsheet, and one of the columns is a Date. The problem is that the date is presented in the format 20091012 and excel does not recognize that as a data. What I need is a formula or VBA code to transfer 20091012 into YY-MM-DD format.

View Replies!   View Related
Converting Date Column To Text Formatting
I have column A set up to display dates in the following format: 03/04/2009. While the "look" is fine for the values in the column, I cannot get it to stay in that format when it is moved to a text file. It simply converts the display back to 39876.

I would like a macro that 1) looks at all values in column A and 2) converts each cell in the column to text format but leaves the contents so they are dates 03/04/2009 (mm,dd,yyyy).

View Replies!   View Related
Join Time & Date & Retain Formatting
I have written some code which takes data from multiple spreasheets and formats it in a specif way. However one problem I have is getting the right data format. As you will see from the input files the date and time cells are separated into two and the macro is supposed to aggregate these together in one cell with a space in between - but I cannot manage to perfect this !!! The output is in a number format instead. I have attached the input files. The code written so far is as follows - works perfectly apart from date format that comes is in the output file once you run the code.

Dim Obj1 As Object
Dim Obj2 As Object
Dim UserList()
Sub RunMe()
Dim Str1 As String
Dim myPath As String
Dim i As Integer
Set Obj1 = CreateObject("excel.application")
myPath = "C:Test" 'please note you should a "" at the end.
Str1 = Dir(myPath & "*.xls")
Set Obj2 = CreateObject("excel.application")
Obj1.Application.DisplayAlerts = False
Obj1.Workbooks.Open (myPath & Str1)
'For i = 1 To UBound(userslist).............

View Replies!   View Related
Conditional Formatting To Highlight 2 Days Before The Expiry Date
I have a list of items which are supplied by 3 persons and these items have expiry date. I want the Conditional Formatting to highlight 2 days before its expiry date. I have managed to set only for dave, how do I set for the other two persons? FYI, each item is only supplied by one person.

View Replies!   View Related
Copyright © 2005-08, All rights reserved