Excel Formula Dates

[krc547]

I have a cell that grabs the date of intake the next cell sees that cell and
puts in the last day of the month. for example if date of intake is 11/3/07
then the next cell shows 11/30/07. What I need is a formula to calculate the
number of days in the program.=DATEDIF(C16,D16,"D"). But if the date of
intake is 11/30/07 the next cell still puts in 11/30/07 and the formula
returns 0 as number of days when I need it to return 1. likewise in the next
set if the date is 12/1/07 and then 12/31/07 I need it to return 31 days.

 

[WHA]

I have a cell that grabs the date of intake the next cell sees that cell and
puts in the last day of the month. for example if date of intake is 11/3/07
then the next cell shows 11/30/07. What I need is a formula to calculate the
number of days in the program.=DATEDIF(C16,D16,"D"). But if the date of
intake is 11/30/07 the next cell still puts in 11/30/07 and the formula
returns 0 as number of days when I need it to return 1. likewise in the next
set if the date is 12/1/07 and then 12/31/07 I need it to return 31 days.

Based on what I'm seeing here, I'd simply tack a "+ 1" onto the end of
the formula.

 

[krc547]

Based on what I'm seeing here, I'd simply tack a "+ 1" onto the end of
the formula.
The only problem is that all the other cells then show a one in the days column wether there is a date or not. This can not happen because depending on number of days, it calculates cost.

 

[DFruge]

Then, test the date cell for a blank. If it is blank then return blank
("")instead of the "1".
based on your other formula:
=if(C16="","",DATEDIF(C16,D16,"D")+1)

 

[Ron Rosenfeld]

I have a cell that grabs the date of intake the next cell sees that cell and
puts in the last day of the month. for example if date of intake is 11/3/07
then the next cell shows 11/30/07. What I need is a formula to calculate the
number of days in the program.=DATEDIF(C16,D16,"D"). But if the date of
intake is 11/30/07 the next cell still puts in 11/30/07 and the formula
returns 0 as number of days when I need it to return 1. likewise in the next
set if the date is 12/1/07 and then 12/31/07 I need it to return 31 days.

Simply: =D16-C16+1

(No need to use DATEDIF for "days")
--ron

 

[krc547]

Then, test the date cell for a blank. If it is blank then return blank
("")instead of the "1".
based on your other formula:
=if(C16="","",DATEDIF(C16,D16,"D")+1)

what if I need to count number of days except if cell d17 is not the end of
the month. Example C17 and D17 are as follows:
c17=11/25/07 d17=11/29/07 instead of it returning 5 days I would need it
to return 4 days because the client was not here until the end of the month.