Calculating new date from keyed in date

  • Thread starter Thread starter bloors
  • Start date Start date
B

bloors

I'd be grateful for any help on this problem...

If a date has been keyed into a cell I want to calculate what the date
will be 21 workdays beyond that date and insert this date into another
cell...

The problem I'm having is with the IF bit - and with the workdays
bit...

Also I'd like to be able to calculate the difference between the
calculated date and todays date - ie in terms of days to go or days
past and if the calculated date has been and gone make the days elasped
since the calculated date value appear in red...

Any pointers would be most welcome!



------------------------------------------------
Message posted

-- View and post Excel related usenet messages directly from http://www.ExcelForum.com
at http://www.ExcelTip.com/
------------------------------------------------
 
Use the WORKDAY function.

Let us say your input date is in cell A1 and the number of
workdays you want to add is in cell B1, and in cell C1 you
want the result. Thus in cell C1 you type the following
formula:

=WORKDAY(A1, B1)

Optionally, if you have a holidays calendar for your
organization and you want the workdays to skip the
holidays also (over and above the weekends) then provide
that as the third parameter (see help in excel for
details).


For finding difference between a given date and today, the
formula in cell d1 is = C1 - TODAY() assuming given date
is in cell C1.

Finally, to change the color of this last result (cell D1)
to RED, use conditional formatting. Set the condition
that if the cell contents are less than 0 then use
formatting of RED colored fonts (or RED background or
anything else).

To see your numbers in userfriendly formats, ensure that
cell formatting is correct. Thus, in above examples
ensure that cells A1 and C1 are formatted in date formats
(mm/dd/yy or dd/mm/yy or any other format you want); for
cells B1 and D1 ensure that they are formatted as GENERAL
or NUMBER.



-----Original Message-----
I'd be grateful for any help on this problem...

If a date has been keyed into a cell I want to calculate what the date
will be 21 workdays beyond that date and insert this date into another
cell...

The problem I'm having is with the IF bit - and with the workdays
bit...

Also I'd like to be able to calculate the difference between the
calculated date and todays date - ie in terms of days to go or days
past and if the calculated date has been and gone make the days elasped
since the calculated date value appear in red...

Any pointers would be most welcome!
Click to expand...
from http://www.ExcelForum.comat http://www.ExcelTip.com/
 
Back
Top