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

User Tag List

 
LinkBack Thread Tools Display Modes
post #1 of 2 Old 01-31-2011, 04:16 PM Thread Starter
Serving Our Country!
 
Gunny_990's Avatar
 
Join Date: Feb 2010
Posts: 47
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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

Charlie
Gunny_990 is offline  
post #2 of 2 Old 02-04-2011, 02:53 AM
Premium Member
 
2008PSDnewbie's Avatar
 
Join Date: Sep 2009
Location: Wetaskiwin Alberta
Posts: 39
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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, 55 views)

Glen

'08 F350, cc, lb, ARB front bumper, Warn 12,000 lb. Spartan tuner, KEM 310 tune , Flow Pro 4", Weatherguard toolbox, 6" PIAA spots, leveling kit.
2008PSDnewbie is offline  
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:
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:
OR

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
Show Printable Version Show Printable Version
Email this Page Email this Page
Display Modes
Linear Mode Linear Mode



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

 
For the best viewing experience please update your browser to Google Chrome