Excel 2007 help - Ford Powerstroke Diesel Forum
Computer Geeks Gaming, Servers, Code, everything computer...

Powerstroke.org is the premier Diesel Truck Forum on the internet. Registered Users do not see the above ads.
Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-31-2011, 04:16 PM
Serving Our Country!
 

Join Date: Feb 2010
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Feedback Score: 0 reviews
Excel 2007 help

Hopefully someone is an Excel 2007 guru and can help me with the following; I have a spreadsheet that basically has 3 columns “ Due Date” , “Date Paid” and “Paid”, depending on what is entered in the “Date Paid” column a statement will display in the “Paid” column. Need to mention that the due date will always be a fixed date such as 31 May 20XX. This is what I need it to do;

- If a date is entered in the ‘Date Paid” col, the “Paid” Col will display “Paid”. No matter what the date entered is, could be any date between now and 31 May 2011..

- If nothing is entered in the “Date Paid” col and it is before 30 days of the due date, the “Paid” col will display Due.

- If nothing is entered in the “Date Paid” col and it is within 30 days of the of the “Due Date” the “Paid” col will display “Warning”.

- If nothing is entered in the “Date Paid col and it is after the “Due Date”, the “Paid” col will display “Overdue”

This is the formula I have come up with,
=IF((TODAY()+30)<J2,"",IF(TODAY()>J2,"Overdue",IF( ISBLANK(I2),"Warning",K2*1)))

Everything works except if I enter a date in the “Date Paid” col nothing appears in the “Paid” col until it is within 30 days of the due date.

If this has to be a VBA, I am good with that, just not sure on how to write it, maybe a loop statement.

Any help is greatly appreciated
Reply With Quote Quick reply to this message
Sponsored Links
Advertisement
 
  #2  
Old 02-04-2011, 02:53 AM
Premium Member
 

Join Date: Sep 2009
Location: Wetaskiwin Alberta
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Feedback Score: 0 reviews
Try this.
The formula will display "Paid" in the Paid column when any date is entered.
If the Date Paid column is empty and the Due Date is 30 days or more in the past, "Overdue" will be displayed.
If the Date Paid column is empty and the Due Date is between yesterday and 29 days in the past, "Warning" will be displayed.
If the Date Paid column is empty and the Due Date is today or in the future, "Due" will be displayed.
Hope this helps.
Attached Files
File Type: xls Book1.xls (28.0 KB, 33 views)
Reply With Quote Quick reply to this message
Sponsored Links
Advertisement
 
Reply

Quick Reply
Message:
Options

Register Now

In order to be able to post messages on the Ford Powerstroke Diesel Forum forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.
User Name:
If you do not want to register, fill this field only and the name will be used as user name for your post.
Password
Please enter a password for your user account. Note that passwords are case-sensitive.
Password:
Confirm Password:
Email Address
Please enter a valid email address for yourself.
Email Address:

Log-in

Human Verification

In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.



Thread Tools
Display Modes

Posting Rules
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT -8. The time now is 12:52 PM.


Powered by vBulletin® Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.6.1
Garage Plus, Vendor Tools vBulletin Plugins by Drive Thru Online, Inc.

vB.Sponsors