Date Calculation with exceptions

C

copgal09

New Member
Joined
Sep 23, 2014
Messages
9
I have created a log that will calculate number of days between an inmate's intake date and release date using Excel 2010.
BUT, when the inmates are taken in, I don't want the day of intake to be counted EXCEPT when the inmate is taken in and released on the SAME date. I don't know how to make the formula count days when there are two or more days, but NOT count the day of intake.
I am using the hidden DATEIF function.
I'm sure this is a "duh" for most of you, but for me it has been a three-day constant frustration.
Currently using:
=DATEDIF(A2,G2,"D") to calculate number of days between dates but can't find the way to add the exclusion of start date when more than 2 days are the returned value. Does this make any sense?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Maybe:

=IF(G2-A2>2,DATEDIF(A2+1,G2,"d"),1)
 
Upvote 0
Joe! That does work...but, now, I have another issue! When NO entry has been made in the rows below, I have a value of "1" appearing in my number of days column, which I need to be a "0", or it will SUM that column incorrectly. Do you have any suggestion for this? I'm just not savvy enough in Excel to be able to logically figure out the syntax needed. Thanks so much for your assistance!
 
Upvote 0
copgal09 said:
Joe! That does work...but, now, I have another issue! When NO entry has been made in the rows below, I have a value of "1" appearing in my number of days column, which I need to be a "0", or it will SUM that column incorrectly. Do you have any suggestion for this? I'm just not savvy enough in Excel to be able to logically figure out the syntax needed. Thanks so much for your assistance!
Click to expand...
Post the formulas, if any, you have in G2 and A2.
 
Upvote 0
copgal09 said:
No formulas. A2 is INTAKE DATE, and G2 is RELEASE DATE. Is there any way to get my workbook to you so you can see what I'm trying to do?
Click to expand...
I will PM you with an email address and you can send me your workbook.
 
Upvote 0
copgal09 said:
Joe! That does work...
Click to expand...
I read your message differently than Joe did as I think his value are one less than they should be. Just to make sure you get what you really need, can you tell me the number of days you expect for the following date ranges...

1) 9/1/2014 to 9/1/2014
2) 9/1/2014 to 9/2/2014
3) 9/1/2014 to 9/3/2014
4) 9/1/2014 to 9/4/2014
5) 9/1/2014 to 9/5/2014
 
Upvote 0
If the "In" day is the 1st of December and the "Out" day is the 5th, what value would you want your "number of days between" formula formula to return - 3, 4 or 5? The number of days "between" the two dates is 3 - the number of elapsed days is 4 and the total number of days is 5!

On the basis that your "In" date is in column A and your "Out" date is in column G, the following formula currently returns 5 for the above dates and 0 if the two dates are the same. Is this what you want?
Code: 
=IF(A5=G5,0,DATEDIF(A5,G5,"D")+1)

Remove the "+1" to return 4 and replace the "+1" with "-1" to return 3

Hope this helps

Pete
 
Last edited:
Upvote 0
Hi, Rick!
Here's the scoop:
1) 9/1/2014 to 9/1/2014 should be 1
2) 9/1/2014 to 9/2/2014 should be 1
3) 9/1/2014 to 9/3/2014 should be 2
4) 9/1/2014 to 9/4/2014 should be 3
5) 9/1/2014 to 9/5/2014 should be 4
Now...those are all working fine using my current formula.
However, the sticky wicket is the rows with NO data entered in the date columns. Once I copy my current formula into the rest of the column cells, the empty rows return a value of "1", which I need to be returned as BLANK, OR NIL, OR at the very least, "0". If it returns a "1", then my column auto sum will be incorrect.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
155
awoohaw
awoohaw
F
  • Question
Trying to only calculate from a start date to today only when a certain cell contains "Pending"
Replies
9
Views
428
Coyotex3
C
SaraWitch
  • Solved
Calculate days between two dates but from the fiscal year if start date precedes April
Replies
3
Views
392
awoohaw
awoohaw
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
437
KRice
K
pboltonchina
  • Solved
Can't get this formula to work
Replies
5
Views
226
RoryA
RoryA

Forum statistics

Threads
1,214,918
Messages
6,122,255
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top