calculate new date & prompt when due

  • Thread starter Thread starter tammyt
  • Start date Start date
T

tammyt

Hi there,

Hope someone help me on this pls....:)

I have 3 columns

Col 1: Key in Start Date
Col 2: Select Period (I display this as a drop down list of say 1 day
5 days, 7 days)
Col 3: Calculate Due Date

How can I automatically calculate the 3rd Column based on Col1 & Col2
and also make it red and bolded if the date is due (ie today or past)?

On a side note, if I have a column of running numbers, ie 1,2,3,4,..
how do I make sure this list is auto extended when I add on new rows?

thanks, much appreciate
 
One way:

if your dropdown list is numeric:

C1: =A1 + B1

format C1 as a date.

If your dropdown list consists of numbers followed by "days":

C1: =A1 + LEFT(B1,FIND(" ",B1)-1)

To make C1's font bold and red, choose Format/Conditional Formatting...

CF1: Formula is =A1<=TODAY()
Format: Bold, Red

One way to extend your column of running numbers, if you're using Mac XL
or XL03 is to use the List Manager.
 
Hi!

Thanks sooooo much!!! It works it works!!!! :cool:

For the 2nd part, sorry, what is a List Manager, where can I find it
Sorry to trouble you so much...

Also, how can I make this formula stick for the next row I insert...E
now that I got C1 right, when I put in A2 + B2, I need to copy th
formula to C2, C3, C4, C5 and so on...instead of doing this one b
one...is there any way I can make it go on and on by itself?

Thanks again! :)
 
Sorry - I forgot that when MS ported the List manager from MacXL to
XL03, it got rid of the term "List Manager". The functionality still
exists, just not the name.

Look up Create a List in XL03 Help.

You can also set your columns in a list to be calculated columns - where
the formula will automatically be added when you add another row of
data. That is also documented in Help.
 
Thanks thanks!! Managed to find it...:) You have been most helpful!

For the conditional formatting,
Initial eg was
CF1: Formula is =A1<=TODAY()
Format: Bold, Red

Can I have 2 conditions in the same formula? Eg, A1 <= TODAY() an
value of cell B1 = No, then format bold, red, else IF B1 = Yes, forma
bold, green?
 
one way:

CF1: Formula is =AND(A1<=TODAY(),B1="No")
F1: Bold, Red

Click Add,

CF2: Formula is =B1="Yes"
F2: Bold, Green
 
Back
Top