Excel 2000 - show billable days

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

try

For the first

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

for the second

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

In I5, try

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

Is every day a billable day, or only weekdays?

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

Originally Posted by 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?

OK

In F5

Please Login or Register  to view this content.

In G5

Please Login or Register  to view this content.

and copy down

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?

Cells must be formatted as date

Both formulae should provide blank if no input - they did when I tested it

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save