How can I calculate the number of days in a time period, but not the "difference"?

Hello,

I've been searching through countless Excel websites for help with this issue, but I can't seem to resolve it. I am designing a spreadsheet that needs to calculate the number of days of an event. The problem with using simple subtraction or the DATEDIF function is that it is always short 1 day. For example, if it is a one-day event (ie date value 1 = 02/23/05 and date value 2 = 02/23/05), the number of days returned is zero, even though the event did occur for one day. This problem is easily solved by adding a 1 to the formula, but there are many cells that use this formula but don't have date values yet, so I'm stuck with a sheet full of 1's (if I hide all 1's, it affects events that actually are only one day). I thought about changing the format of the cell to automatically add the 1 day, but I wasn't sure how to do it. The only way I could adjust the formula left the cell looking like #+1, which looks silly. Any suggestions?!

Thanks in advance for your help - people here were a great resource last time I had an issue.

Try this:
Start date in A1, end date in B1, this formula in C1: =IF(B1-A1+1=1,"",B1-A1+1)
It seems to work with USA and UK date formats. mm/dd/yy and dd/mm/yy. If A1 and B1 are empty, C1 is blank. USA 02/23/05 and 02/25/05 gave 3 days. UK 22/02/05 and 25/02/05 gave 4 days.

Thanks for the tip, much appreciated!! This solves the issue of having a 1 appear in rows that lack date columns, which is a large improvement.

One small problem exists, however, which are events that only occur for one date. The start and end date are the same, which gives a value of 1 (which is the correct answer). However, b/c the value is a 1 the cell appears blank. Is there any way to remedy this?

Unfortunately, I'm not that experienced at combining complicated formulas, etc. The only way I've found so far is to add in column D this formula: =IF(B1-A1=0,1,"") and copy down. If a start and end date are the same a 1 will appear in column D and column C will be blank. That's the best I can offer at the moment. Hopefully, someone will supply a better formula.

Thanks for the suggestion. I'd rather not have the extra column, but it's a good thought. Perhaps somebody else will have another idea.

Thanks again for all your help.

You could test if the any of the two cells are empty:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",B1-A1+1)

Ola Sandstrom

Thanks for the suggestion OLA - I just figured something out that also works:

=IF(AND(B1=0),"",C1-B1+1)

Thanks everyone for your help!

I like your suggestion better than mine Ola - this would work well if someone knew the start date but not the end date, for example.

Thanks again!

Gareth-

You're welcome.
Thanks for the feedback.
Ola