calculate new date & prompt when due

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

--
Message posted from http://www.ExcelForum.com

0
8/18/2004 3:44:30 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
458 Views

Similar Articles

[PageSpeed] 7

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.




In article <tammyt.1b60s4@excelforum-nospam.com>,
 tammyt <<tammyt.1b60s4@excelforum-nospam.com>> wrote:

> 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?
0
jemcgimpsey (6723)
8/18/2004 4:52:38 AM
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! :)  

JE McGimpsey wrote:
> *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/Conditiona
> Formatting...
> 
> CF1:    Formula is  =A1<=TODAY()
> Format: Bold, Red
> 
> One way to extend your column of running numbers, if you're using Ma
> XL
> or XL03 is to use the List Manager.
> 
> 

--
Message posted from http://www.ExcelForum.com

0
8/18/2004 8:12:04 AM
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.



In article <tammyt.1b6d62@excelforum-nospam.com>,
 tammyt <<tammyt.1b6d62@excelforum-nospam.com>> wrote:

> 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...Eg
> now that I got C1 right, when I put in A2 + B2, I need to copy the
> formula to C2, C3, C4, C5 and so on...instead of doing this one by
> one...is there any way I can make it go on and on by itself?
0
jemcgimpsey (6723)
8/18/2004 1:14:49 PM
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?



JE McGimpsey wrote:
> *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. 

--
Message posted from http://www.ExcelForum.com

0
8/19/2004 4:17:46 AM
one way:

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

Click Add,

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


In article <tammyt.1b7wzk@excelforum-nospam.com>,
 tammyt <<tammyt.1b7wzk@excelforum-nospam.com>> wrote:

> 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() and
> value of cell B1 = No, then format bold, red, else IF B1 = Yes, format
> bold, green?
0
jemcgimpsey (6723)
8/19/2004 1:56:07 PM
Reply:

Similar Artilces:

Inserting Date/Time of Last Update
Is there a way to insert a date and time in a cell(s) that will only be updated if there is a change to the worksheet. I'm looking to have the date/time of the last update so that it is easy to tell how current the worksheet is. Thanks -- Lionel B. Dyck <>< AIM ID: lbdyck Yahoo IM: lbdyck Homepage http://www.lbdsoftware.com/ Blog: http://randommgmt.blogspot.com/ Lionel You could put some code in a worksheet_Change() event like so (uses A1) Private Sub Worksheet_Change(ByVal Target As Range) Range("A1").Value = Now() End Sub To implement right click the sheet t...

Update Promised Date on Item Quantities Maintenance
I don't know why, but when the Promised Date is changed on a Purchase Order, the Promised Date displayed on the Item Quantities Maintenance screen does not get updated. This date is important for our salespeople to see to let customers know when we are expecting certain items in, and it should always be up to date. ---------------- 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 this link to open the sug...

Counting Distinct Dates with Criteria
I need to count the number of days that correspond to two other criteria. The following formula will count the distinct number of dates: =SUMPRODUCT(--(B605:B3000<>"")/COUNTIF(B605:B3000,B605:B3000&"")) But if I put that into a formula like the following it doesn't do what I want: =SUMPRODUCT(--(H605:H3000=B9),--(G605:G3000=C9),--(B605:B3000<>"")/COUNTIF(B605:B3000,B605:B3000&"")) 10/17 ven x 10/17 ven x 10/18 ven y 10/19 ven z 10/19 ven x 10/20 ven x 10/20 ven x 10/20 ven x 10/20 ven z Here'...

All PIDS Used but need to re-install on new computer
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel We have a student edition with three installs already. However one of the Macs had to have a hard drive replaced due to failure and now we that we have re-installed we find that this PID is on conflict with the other ones. Apparently when I activated this re-install it used the same PID as one of the other installs. Our son (the student) has no idea wher his packaging. Is there a way to resolve this issue. it seems to me like Microsoft's activation is saying we activated too many installs? Rick Nash In article <5...

Issues with new document in Word 2007
The user has two documents open in Word 2007. He is copying and pasting from an existing document to a new one. Here are the problems that he is having with the "new" document: 1) If he selects one word to make Bold then ONLY that word will be affected. However, if the user chooses more than one word to Bold then the entire page becomes Bold. The user will then utilize the "Undo" feature and what he originally wanted to be Bold turns out that way. 2) Bullet points: The user will place his cursor next to a sentence, click on the Bullet point option, ...

New CRM 1.2 Exams
It appears that Microsoft have withdrawn the 1.0 exams and have replaced them with new 1.2 versions US MS-03-020 Microsoft CRM Customization v1.2 US MS-03-030 Microsoft CRM Applications Professional v1.2 No word on the new Installation and Configuration exam Julian Julian Sharp Vigence for MS CRM in the UK These will not have to be retaken if you have passed the version 1.0 exams. This will change for version 2 however "Julian Sharp" <news@nospam.sharpuk.f9.co.uk> wrote in message news:cmgo205fu3r9ae177aopuoqhclpcbtrclj@4ax.com... > It appears that Microsoft have wit...

Calculating Time difference based on a condition
I have 3 columns that have date/time on the first column and device status on the 2nd column and calculated Time difference on the third column. I have sample data below. I have to subtract the first instance of ENABLE DEVICE DATE/TIME from the previous MALF DEVICE DATE/TIME to get the difference. I do not worry about the DISABLE Device date/time at all. I could do this manually but there are about 20,000 records to go through. I don't know if there is a worksheet function that will do this automatically for me. Date/Time DEVICE STATUS TIME DIFFERENCE 6/7/04...

How do I stop today() from updating date on saved spreadsheets?
I have a template that automatically populates the date field with the current date when opened, using today(). Users then Save As to have an archive copy of their spreadsheet. However, when they go back to access their saved sheet, the date changes from the created date to the current date. How do I stop this in Excel? I know that Word lets you do this by changing the code from {DATE} to {CREATEDATE}, but I can't find a similar setting in Excel. Please help and thank you! have it do range("a1")=date -- Don Guillett SalesAid Software donaldb@281.com "lionmark&...

When is new version coming?
Any idea when a new version will be out? A small child turns to Ed, and exclaims: "Look! Look! A post from rbrookstn@charter.net <rbrookstn@charter.net>!"... > Any idea when a new version will be out? Publisher 2003 is the newest version of Publisher, and was released on October 21, 2003. I would not expect a new version for a while now. -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm Well, that sucks! -- Brian Kvalheim Microsoft Office Publisher MVP Officia...

Q: date convert
Hello, How can I convert the date with the format YYYYMMDD to MM/DD/YYYY with a formula? Thanks, Hi With date in A1 =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) and format in any valid date format -- Arvi Laanemets ( My real mail address: arvil<at>tarkon.ee ) "JIM.H." <JIMH@discussions.microsoft.com> wrote in message news:B26BAFEA-7815-4504-9CDC-1BBA2789D2FF@microsoft.com... > Hello, > How can I convert the date with the format YYYYMMDD to MM/DD/YYYY with a > formula? > Thanks, > Hi Jim Try =MID(A1,5,2)&"/"&LEFT(A1,4)&"...

wont calculate
i have a spreadsheet that allows me to enter information and formulas but it will not calculate. Is there are setting that may need to be changed? Thanks for the help. HIt F9 to see it it calculates, if it does then go to: Tools>Options>Calculation tab see if the maunal option is selected and change it to automatic. If it doesn't you may wants to also make sure that your spreadsheet does not have any circular refrences. "john mcmichael" wrote: > i have a spreadsheet that allows me to enter information and formulas but it > will not calculate. Is there are...

Can Excel notify me when a date is due?
I am currently setting up an Excel spreadsheet to calculate dates, and I was wondering whether Excel could notify me somehow 2 days before a date I have calculated, or to somehow flash up that the date is due. Does anyone know how to do this, or would I somehow have-to transfer the dates I have calculated in Excel into Outlook? Hi Claire Use a helper cell with A1-2 and use that cell in the change event Try this http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Claire Chandler" <Claire Chandler@discussions.microsoft.com> wrot...

condition format based on lapsed dates
I want to format a group of cell based on days old. The days is based on the "value" in cell I8 =Now() and cell J10 (j11,j12...) on is the submission date. Therefore number of days that has passed is in cell I10 (i11,i12...) =$I$8-J10" in the cell, this is the number of days that has lapsed. So if 90 days has passed I want to grey out cells k5,l5,m5, and n5. I am unable to get this to work, what do I need to do to make this work? I am assuming that the value is not showing correctly in the formula portion of the conditional format: if cell(I10)>= 90 then [grey out cell...

Excel not calculating anything
Hello, I just installed excel 2003 and when I type in a function (specifically, =CONCATENATE(A2," ",B2) ) It just adds that to the cell I'm wanting the results in. It does this with sums as well. I am very novice at excel, but I do know how do do the minor functions. Can anyone tell me if there is some option I have unclicked or clicked that won't let excel calculate anything? Thank you Hi It looks like you have the calculation set to manual {press F9 to calculate formulas manually, or check Automatic in radio button on Calculatin tab of Options window (Tools.Options ...

Finding most recent date for each customer
I have a simple list, the left column is the customer number, the right column is the date of every sale made to the customer. I need to filter this list to show the most recent date only for each customer. The list has many hundreds of customer numbers with between 1 and 200 dates per customer. There are no blanks in the list eg. 1234 25/10/2009 1234 26/10/2009 1234 03/03/2010 1356 23/12/2009 1356 25/04/2010 1999 06/02/2009 2157 09/05/2009 2157 10/06/2009 2157 25/11/2009 2157 23/04/2010 result would be 1234 03/03/2010 1356 25/04/2010 1999 06/02/2009...

Calculating rents and months past in 1 cell???
Hi, I´m making a Worksheet to calculate the Residual Value of car at the time the worksheet is open. So i have colums with the "inicial date of the contract", "Final date of the contract", "the monthly rent", and the "car value when purchased". With this colums i need to calculate the value of the car at today date. This columm as to do this: "Value of the car when Purchased" - (MINUS) "rents payed since the begining of the contract until todays date". How can i make excel calculate this. Sincerely To find out the num...

outlook XP does not show new messages
I have this problem now on serveral machines, all runing windows 2000 SP4, and outlook 2002 SP3 conected to a local Exchange 2000 Server. Emails do not display automatically. They apear when clicking on an existing email, changing folders, creating new mail. They will not appear if you run send and recieve. Avts as if emails are their but it does not notify user that they are there and does not display them. All the machiens are set up to notify user of new mail, but it does not even do that. Only a handfull of machines with three different hardware configurations are doing it. An...

Reply, Forward, New Mail Fonts Bigger
I was trying to copy something using my keyboard shortcuts, ctrl + C and obviously fat fingered something. I suddenly saw all the fonts get bigger. When I receive an email, it looks normal, but as soon as I go to reply, everything is suddenly bigger - even the original. This happens on reply, forward, and new emails. I don't know what keys I hit, I don't know what it's called to find a setting and I don't know what to look up. Anyone know what I did so I can change it back to normal? Thanks so much, Trisha On the Format Text tab, Select Zoom. TJAC wrote: ...

Excel file modification date
In Excel 2002 SP3 on Win XP, the workbook's file modification date changes to the current date and time as soon as the Excel workbook is opened, even if there are no changes applied to the file. If you close the workbook without changing any of its contents, the file modification date reverts back to the original value. Is there any way for the file modification date to remain unchanged until afte the Excel workbook has been modified? Does this behavior occur in newer versions of Excel? It has been that way for as long as I can remember and I don't think it's changed in XL20...

Formulas
I want the daily average to appear at the point when the date changes. Is there a way to do this? For example, lets say I want to use columns A, B, and C. A will obviously contain the date, B will contain the data, and C will contain the average up to (and including) the date it belongs to... Here is an example: 5 Oct | 50 6 Oct | 100 The above example shows columns A and B, and for C1 I want it to average only B1, which would be 50. For C2 I want it to average B2 and everything that occured before then, so B2 and B1 >> 50+100=150 / 2 = 75 (It found the av...

calculate hours over multiple days
How do I..... Date // time calculation I have something that starts on a certain date at a certain time --- and goes for several hours --sometimes over several days and has an end date and time which I can enter. I want to calculate how many total hours:minutes �(so hours will sometimes be in excess of 24) For example -- want to enter 9/28 8:30PM - 10/9 6:00AM and have it calculate the total hours & minutes from begining to end. (I want to be able to enter the date & time for beginning and end points and have the results as HH:MM) THANX!! "Larry Geyer" <larry...

Is there an ROI calculator for Microsoft Retail Management System.
Is there an ROI calculator for Microsoft Retail Management System? If so where can I download it? not sure if this is what you are looking for or not. www.gmroii.net "georgesg" <georgesg@discussions.microsoft.com> wrote in message news:EA22D802-0A5A-4D71-8608-DE0B9C17E636@microsoft.com... > Is there an ROI calculator for Microsoft Retail Management System? If so > where can I download it? ...

actual posting date payables management
They would like to have the ability to view the system date/actual date when the payables transaction was posted. Currently the 2 posting dates in the PM20000 is not the actual date. ---------------- 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 this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolu...

New Territory Strategy
Our Sales executive had a bright idea last week to revamp all of the territories in the Sales Department without talking to anybody about it outside of Sales. We are a volume based business so for under 20,000 volume, we have reps for three areas of the U.S. Over 20,000 we have reps for eight areas of the U.S. What is the best way to map that out in CRM? Thank you. Here some ideas: 1. Determine at the Account level when category it falls under, under 20k or above 20k. 2. You have a total of 11 areas (3 + 8) 3. Within an Account, need a flag that will indicate its volume and some o...

List box and command button to display results in new form
I am missing something. In Access 2003 I never had this problem, but now I am using 2007. In a form I would create a list box that would display one column from a table. Then I would create a command button using the button wizard that would then open a form which would only display certain records, then I would link the list box to the appropriate column in the table and it would display the correct results according to what was selected in the list box. In 2007 I can not link the listbox to the appropriate column in the table in the wizard. Am I missing something when I create t...