Excel 2000 - show billable days

[php4u]

I have been asked to create a spreadsheet that will contain three fields;

Date of placement (the day the person arrived)
Date of release (the above + 60 days)
Billable days for the current month

The first is simple, the clerk will enter the current date when the person arrives.

For the second field I am using:: =SUM(H5+(60),) (H5 is todays date, and will display the results in I5). When H5 is blank, I get the " #VALUE!" error in I5? Is there a more effective function to use?

For billable days, I am using; =SUM(J2 - (H5),) (J2 is simply the last date of the month; 12/31/09, and H5 is the placement date). I want to show the number of billable days in the current month. This will be repeated on 12 sheets (one for each month).

Any suggestions greatly appreciated,

Thanks!
Ralph

[EdMac]

try

For the first

=if(H5="","",H5+60)

for the second

=if(H5="","",J2-H5)

[royUK]

In I5, try

=IF(H5,H5+60,"")

Is every day a billable day, or only weekdays?

[php4u]

EdMac and royUK, thank you very much for the prompt response, this forum is verrry helpful!

Ok, I am getting the same result (#VALUE!) in the empty cells. See the attached file; (ALL days are billable)

H column - is a field where the clerk will enter the date the person arrives.

I column - is the field that has a formula that heads H and adds 60 days.

Cell J2 contains the last date of the month (12/31/08), which is used to calculate the next column - billable days.

Billable days is J5-J50, which should subtract H from J2? (e.g. =IF(H5="","",H5+60)

The bottom of the sheet simply totals the columns.

Thanks a bunch, we are almost there!
Ralph

[php4u]

EdMac and royUK, thank you very much for the prompt response, this forum is verrry helpful!

Ok, I am getting the same result (#VALUE!) in the empty cells. See the attached screen-shots; (ALL days are billable)

H column - is a field where the clerk will enter the date the person arrives.

I column - is the field that has a formula that heads H and adds 60 days.

Cell J2 contains the last date of the month (12/31/08), which is used to calculate the next column - billable days.

Billable days is J5-J50, which should subtract H from J2? (e.g. =IF(H5="","",H5+60)

The bottom of the sheet simply totals the columns.

Thanks a bunch, we are almost there!
Ralph

Is there a way to hide the "#VALUE!" errors so the totals will calculate correctly? Or is my formula incorrect?

[EdMac]

OK

In F5

=IF(ISERROR(E5+60),"",E5+60)

In G5

=IF(ISERROR($G$2-E5),"",($G$2-E5)+1)

and copy down

[php4u]

Thanks EdMac!

The 1st formula provides a static date of " 2/29/00 " which is ok I suppose.

The second formula provides a result of " 39814 " which I think is some sort of date series (format) used by Excel. The problem with the readout is that it adds erroneously to the total. If there is no data in the row, then the cell should read blank, true? However, when I type in the "Placement" date in the proceeding column, the billable days column calculates the result correctly. - correct calculation, incorrect empty readout.

Is there an argument we can add to to suppress the output if the cell is blank?