Conditional Formatting w/ Dates
I would like to format a set of cells on a condition using dates. For
example, in cell A1 I have 5/23/05. In cell B1 I have 6/19/05. In cell
C1 I want to have the condition dependent upon the difference of cells
A1 and B1. How do I subtract the dates to give me the condition I am
looking for. Let us say the conditions are: less than 7 days is green;
greater than 7 days, less than 14 is orange; greater than 14 days is
John F.M.'s Profile: http://www.excelforum.com/member.php?action=getinfo&use...highlight cell with colour if date overdue
can anyone tell me please how do i highlight a cell in a spreadsheet with
colour if date overdue
And how exactly should XL determine that the date was overdue?
Take a look at Conditional Formatting in XL Help.
If, for instance, the due date is in A1 and the date is overdue if it's
in the past, select A1, choose Format/Conditional Formatting... and use
In article <3B7B11E7-894B-458A-BDE6-7710E0743D31@microsoft.com>,
Harvey <Harvey@discussions.microsoft.com> wrote:
> can anyone ...VERY URGENT: storing date in main form
(see previous thread)
Sorry in advance for re-posting so soon. I really need to sort this out.
When I enter a date into the continuous subform, the date field on the main
form gets updated with the most recent date before the field on the subform
was updated rather than the most recent date after it has been updated (if
that makes sense)
I need to somehow requery or save the record but I can't see how to do it.
"Loose Change 2nd Edition" has been seen by almost 7 million people on
You realize, don't you, that the folks here are volunteers...how to automate min and max on axis based on calculation
While automating some chart making with code, I am trying to automate the
span of time reflected on the x-axis. Auto doesn't work because I need the
beginning to be based on an October of a given year. Similarly I have
requirements for the max.
The appropriate min and max are calculated elsewhere, but I am not sure how
to code it to accept the value from a cell.
Any help would be much appreciated.
This has to be done programmatically. For a ready-made solution see
www...How do I change the date format?
If anyone could help, I would be appreciative... I have 800 rows of data
where the date is currently formatted:
1. YYYYMMDD How do I change this to MM/DD/YYYY?
2. Telephone number is currently formatted as 7607236300. How do I change
this to 760-723-6300?
Thank you for helping me.
Select the column with the dates
Data|text to columns (xl2003 menus)
Choose Fixed widths, but don't include any separator lines.
Choose Date (ymd order)
and finish up.
Change the number format (format|cells|number tab) to the format you want
#2. Select the range t..."Calculate" appears in staus bar
In Excel 2003, what does it mean when "Calculate" appears in the left
side of the staus bar. I checked options, and calculate is set to
automatic. In most instances, calcuations are automatic. but i have
lately had one workbook that switches to manual calculate. But not
always. I did open a file that was created in XL2010. But it had no
macros. When I see "Calculate" in the status bar, what does it mean?
"Tonso" <email@example.com> wrote in message
> In Excel 2...formula for age at future date
A1 is date of birth in 01/15/30 format. Need to show how old each person
will be (years only) by end of 2010. Then need to indicate Y or N if they
will be 65 or older at that time.
MEJ try this.
A1 is DOB. Type in B1 Today()
Type this formula in C1 =YEAR(B1)-YEAR(A1)
Type this in D1 =IF(C1>59,"Y","N")
This will give you a N for anyone under 60 and a Y for older then 60.
Hope this helps,
> A1 is date of birth in 01/15/30 format. Need to show how old each person
> will be (years only) by end of 2010. Then need to indicate...Difference between % Gain and Total Return for All Dates?
Could someone tell me what the difference is between % Gain and Total Return
for all dates? Which tends to be a more accurate picture of my portfolio
...how do i format multiple cells to calculate a sum?
hi there, im am relatively new to excel and use it only to enter and
store herd lists for my farming. i have to enter a large amount of
information for each cow but have been told that there is a way to
automate some of the calculations that need to be made. here is an
HERD NO M/F COL D--O--B First Check date
10265 M B 05/03/02 01/10/02
the first check date is calculated by the sum "=(E2+210)" Where E2 is
the D.O.B. and 210 being days. the calculation gives the result simply
as a date.
my question is that if i enter the nessesary information su...WTF?? Converting dates from XP to 2003
Ouch...When I read in a spreadsheet in XP, the dates and months are opposite
and all my date data gets messed up.
Seems that one is fomatted as dd-mm-yyyy, and the other is mm-dd-yyyy.
What is going on here, and how to fix it?
what kind of file are you importing?
> Ouch...When I read in a spreadsheet in XP, the dates and months are
> opposite and all my date data gets messed up.
> Seems that one is fomatted as dd-mm-yyyy, and the other is
> What is going on here, and how to fix it?
Probabl...stopping the date from changing in Excel (and Google Spreadsheets)
Wehn I enter "October 10, 2006" into my excel spreadsheet, it always changes
it to "10-10" or something like that. Where can I set my excel spreadsheet to
display the full date?
Also, does anyone have any experience with Google spreadsheets? Any good?
Go to Format, Cells, Number, Date and pick the date format that works for you.
> Wehn I enter "October 10, 2006" into my excel spreadsheet, it always changes
> it to "10-10" or something like that. Where can I set my excel spreadsheet to
> display the...Charting Dated Data
I have a small file, using only 6 rows and 6 columns. A1 has the word Date
in it, A2 through A6 has the dates, 1/1/08, 1/8/08, 1/15/08, 1/22/08, and
B2 through B6 has person's names and the cells from B2 through E6 contains
numerical data. I am attempting to create a column chart, with the weekly
dates on the X axis and the names in the legend, each column represents an
The problem is I am getting a lot of dates between the entered dates, every
other day is showing up on the X axis, cluttering up the axis and setting the
column widths very narrow. The gap wid...Filtering Excel 2003 Charts to a date range
I have multiple charts that we look for trends on over 1-2 year time frames.
Is it possible to have a chart show all the data that has been entered and
when looking at the chart have a date range data to format that range
differently. What I am trying to do is we have weekly or montly meetings, in
these meetings I would like to show all the additional data differently that
has been added since our last meeting, while showing accumulative data in a
The easiest way to get different formats is to use different series. If you
had one series, you could use autofiltering to...I enter date 082305 xl changes it to 5/4/25
I set the date format for the cell as: 8/23/05. When I enter date 82305, I
get 5/4/25. I've checked everything I can think of but I haven't a clue as to
what is causing this. Thanks for all help,
> I set the date format for the cell as: 8/23/05. When I enter date 82305, I
> get 5/4/25. I've checked everything I can think of but I haven't a clue as to
> what is causing this. Thanks for all help,
You will have to enter the slashes to get the correct date. Excel calculates
date from the 1/1/1901 and this is given the refe...Date Modified sort issue.
The issue is that when I sort (right-click --> arrange icons by --> Modified
or click on the Date Modified header), it sorts by Date Modified, but
categorizes by the Name (Folders, Shortcuts, Files. etc.).
I was wondering if anyone knew of a way to sort an entire folder (either
ascending or decending) by Date Modified.
Thank you, WHEELS
-------- Original-Nachricht --------
> Good afternoon.
> The issue is that when I sort (right-click --> arrange icons by --> Modified
> or click on the Date Modified header), it sorts by Da...Invalid Argument Error when using 1/1/08 as a date
Not sure if this is a known issue - can anyone else create a record that
contains a date field with the value of 1/1/08 or 1/1/07, save it and then
open it again without getting an "Invalid Argument" error. I am able to
produce this error across different environments, different entities and
different date fields.
Would be interesting to hear more about this.
Just to let you know, I just created a task in CRM 4 and set the due date to
1/1/08 and it worked without any issues. This is a field displayed as a
Date/Time box, on not just Date. I don...Calculating Time 05-21-10
I'm setting up a timesheet but when I enter the format to + or - if the
'time' is negative the figures are not showing - is there a way around this?
Only by turning on the 1904 date system, which I wouldn't suggest :)
"Fie M-C" <FieMC@discussions.microsoft.com> wrote in message
> I'm setting up a timesheet but when I enter the format to + or - if the
> 'time' is negative the figures are not showi...Auto moving by Date in Outlook 2002
Does anyone know if you can tell Outlook to move emails
older than 90 days to a folder you specify in Outlook and
then in another 30 days delete the emails in that folder?
I'm running Exchange Server 2000 on the server and Outlook
2002 on the client pc's. I can't find anyway in Outlook
to do this using the rules wizard or Auto Archive. I
don't think there are setting to tell Exchange to do this
instead of Outlook. But I'm sure there has to be a way.
...More Date problems
I have a macro that deletes returned records older than 7 days. It first sorts the data into returned date order then checks to see if the first cell is empty (no records returned) and if so, re-sorts the data into the original order (a different date) and quits. If the first cell contains a date less than 7 days old ie date >today()-7, again it re-sorts and quits, otherwise it deletes the data older than 7 days, re-sorts the remaining data and quits. At least that what is supposed to happen
The line to check the age of the date in the macro below doesn't work. G6 contains a control ...Formulas to (a) pull month, year and (b) sum within date range?
I have a list of dates in column A, and numbers down cols B-E. Off to
the right, I need a table that lists the month/year and the totals for
each of the columns. It needs to be a separate table, rather than a
pivot table, so it can be copied-and-pasted by others.
What I _think_ I need is:
-- a formula in H2:H10 (maybe more) that will look down the full range
of dates (call it A2:A100) and put one month/year per row
>> H2 = 02/09
H3 = 03/09
H4 = 04/09
-- a formula in I2 that will sum all entries in B2:B100 with the month
and year of H2
I think I could get the secon...Automatic OverTime Calculation #2
Based on a bi-weekly payroll, if an hourly employee fills in 41 hours the
first week and 39 hours the second week then I would expect to see 1 hour of
overtime for the first week. Instead the software will not calculate
overtime in this case since the hours add up to 80.
Overtime needs to be applied to each week of the bi-weekly pay period.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow th...changing date formats
i need to process data in excel from a 3rd party report that gives th
timefor different departments in the format dd:hh:mm:ss and i need t
be able to get excel to work out the time in hours and minutes so i ca
analyse it further
thnx in advanc
Message posted from http://www.ExcelForum.com
On Sun, 1 Feb 2004 04:19:57 -0600, chris.howes
>i need to process data in excel from a 3rd party report that gives the
>timefor different departments in the format dd:hh:mm:ss and i need to
>be able to get excel to work out t...How do I create an alert of expiration date in Excel?
I created a spreadsheet to track documents. The last column is for
expiration dates of documents. How can I set up an alert when the expiration
date is approaching?
Use conditional formatting in the cell. For example, with the date in A12,
Formula is =TODAY()+7>=$A$12
as the condition.
Then choose a pattern.
> I created a spreadsheet to track documents. The last column is for
> expiration dates of documents. How can I set up an alert when the expiration
> date is approaching?
...The no. of decimal places used in a calculation
Without usign ROUNDUP or DOWN , is it possible to calculate a formul
using the figure shown in the cell (i.e 2 decimal places) rather tha
the underlying figure which could be to many more places?
How many decimal places does Excel work to
Message posted from http://www.ExcelForum.com
I believe Excel works to 15 decimal places. If you only want to use the
places shown on your sheet you'll need to go to Tools / Options /
Calculation and check 'Precision as displayed'. Be warned, this will lose
all the other decimal places and you cannot undo this to get them back!
And...Disable calculation while spinning
A spreadsheet has a Spin button that is linked to a cell. I want th
user to have the possibility of either to spin or to enter a valu
directly in the linked cell. So far, so good. Except that th
spreadsheet recalculates every time whilst spinning. As the amount o
calculation is quite important, the spinning is seriously slowed down
and the spreadsheet user's blood pressure is accordingly up. ;)
I tried implementing a boolean on the spreadsheet module level and t
test/toggle it at the change/spinup or down event. But I hit upon
logical conundrum in that the order of events is first Change...