PDA

View Full Version : Solved: Sinple question regarding calculating a due date...



karlo16
04-02-2009, 06:45 PM
Hi...
First of all thanks ahead of time for your help!

version: Excel 2003

I'm trying to create a spreadsheet to keep track incoming information. It will be added to daily by myself and others. The main purpose of the spreadsheet is to keep track of a due date that is 180 days from the date that a court order is filed. I have a formula in column C which automatically calculates a due date, which is 180 days from whatever date is entered into column B.

Here's the formula I'm using: =DATE(YEAR(B6),MONTH(B6),DAY(B6)+180)

The problem is that even if nothing is entered into column B, a due date (6/28/1900 - which I'm assuming is 180 days from 1/1/1900) automatically appears in column C. I want column C to be blank (but still contain the formula) so that it doesn't confuse the other people who will be entering info into this spreadsheet. I'd also like to be able to protect the formula in column C from accidental deletion...

Can anyone tell me what I'm doing wrong? As much detail as possible will be appreciated, as I'm still kind of a novice when it comes to creating spreadsheets.

I've attached an example of what I'm talking about...

Thank you so much for any help you can offer!

Karl

GTO
04-02-2009, 08:03 PM
There is probably a slicker way of doing this, but this should leave the cell empty if the corresponding cell in Col B is empty.

=IF(B2<>"", B2 + 180,"")

As long as the cells in Col C are formatted for a date, I think you can simply add the value of Bxx and 180. The IF simply tests for IF the cell in B is not empty, THEN add the 180, ELSE "" (leave the cell blank).

Hope that helps,

Mark

Bob Phillips
04-03-2009, 07:05 AM
There is probably a slicker way of doing this...

Don't be silly, that formula is so simple (and that is meant in a complimentary way, simple is good, complex is not so good) that it is highly unlikely that there is a slicker way :)

lucas
04-03-2009, 10:50 AM
I agree with Bob, that's a useful way to hide zero's using just a formula where some people won't accept a workbook with macro's.....

karlo16
04-03-2009, 12:02 PM
It works perfect!

Thank you SO MUCH, Mark! I really appreciate your help... you guys on this forum are all great!