Excel Formula Dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

krc547 - 30 Nov 2007 00:33 GMT

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.

Reply to this Message

WHA - 30 Nov 2007 00:39 GMT

> 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
[quoted text clipped - 3 lines]
> 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.

Reply to this Message

krc547 - 30 Nov 2007 00:53 GMT

> > 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
[quoted text clipped - 7 lines]
> 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.

Reply to this Message

DFruge - 30 Nov 2007 01:07 GMT

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)

>> > I have a cell that grabs the date of intake the next cell sees that
>> > cell and
[quoted text clipped - 14 lines]
>> column wether there is a date or not. This can not happen because
>> depending on number of days, it calculates cost.

Reply to this Message

krc547 - 30 Nov 2007 16:24 GMT

> Then, test the date cell for a blank. If it is blank then return blank
> ("")instead of the "1".
[quoted text clipped - 19 lines]
> >> 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.

Reply to this Message

Ron Rosenfeld - 30 Nov 2007 01:57 GMT

>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
[quoted text clipped - 3 lines]
>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

Reply to this Message