excel days counting formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

krc547 - 30 Nov 2007 16:26 GMT

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.
But I currently have this in my cell: =IF(C17="","0",DATEDIF(C17,D17,"D")+1)

Reply to this Message

Bernard Liengme - 30 Nov 2007 18:39 GMT

How about:
=(D17-C17)+(DATE(YEAR(D17),MONTH(D17)+1,0)=D17)

The first term computes the number of days - there is no need for DATEDIFF
This give 4 in the case or 25 Nov to 29 Nov and 5 in the case or 25 Nov to
30 Nov.

The second then checks is the D17 date is the end of the months and, if so,
adds 1

best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> what if I need to count number of days except if cell d17 is not the end
> of
[quoted text clipped - 4 lines]
> But I currently have this in my cell:
> =IF(C17="","0",DATEDIF(C17,D17,"D")+1)

Reply to this Message