Excel Formula Dates
 

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.

Excel Formula Dates
 

On Nov 29, 6:33 pm, krc547 wrote:
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.

Excel Formula Dates
 

"WHA" wrote:

On Nov 29, 6:33 pm, krc547 wrote:
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.
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.

Excel Formula Dates
 

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)

"krc547" wrote in message
...


"WHA" wrote:

On Nov 29, 6:33 pm, krc547 wrote:
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.
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.

Excel Formula Dates
 

On Thu, 29 Nov 2007 16:33:02 -0800, krc547
wrote:

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

Excel Formula Dates
 

"DFruge" wrote:

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)

"krc547" wrote in message
...


"WHA" wrote:

On Nov 29, 6:33 pm, krc547 wrote:
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.
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.

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.