days cycle elasped

D

dav41sx

New Member
Joined
Jan 3, 2008
Messages
7
Hello everyone!

I am trying to determine the cycle time between two date fields. I know cells (=L2-I2) will show the cycle amount of days. But I would like for it to look at a different scenario. I need it to look at possibly three cells to use the right date.

It will look and choose from three cells; I2, J2, or K2 when the total from the difference doesn’t add up to the total days which ever the month is in cell L2 greater than 31 or the total of days for that month. . If either cell I2, J2, and K2 are null it will skip the nulls and choose the lowest date from the three. If all are null it will use cell L2.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
I don't quite follow that, can you provide some samples and expected results?
 
Upvote 0
Ops sorry for the late reply:
I'm trying to pick from the lowest date from cells I2, J2, K2 to tell the difference from L2 if I2 is greater than L2. If so it will look for the lowest in cells J2, or K2. If J2 or K2 is still greater than L2 it will choose L2 again. If all are null it will use L2 again. If any from the first three are greater than L2 it will use L2 again.

Here's what I go so far...

=IF(OR(J2<K2,I2>L2),0,(MIN(J2,L2)-MAX(I2,K2)+1))

Hope you can help!
 
Upvote 0
Is this what you need?

=IF(COUNT(I2:K2)<3,L2,MIN(IF(I2:K2<L2,I2:K2,L2)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter
 
Last edited:
Upvote 0
Trying this again:

Is this what you need?

Excel Workbook
IJKL
1
21/13/2007
3
4
5
6
71/13/2007
Sheet1

<?XML:NAMESPACE PREFIX = L2,I2 /><L2,I2:K2,L2),L2))< p><L2,I2:K2,L2),L2))< p></L2,I2:K2,L2),L2))<></L2,I2:K2,L2),L2))<>
 
Last edited:
Upvote 0
Okay that almost did it but now it's selecting everything in cell L2. It should only select this if cells I2, J2, K2 are greater than L2. With the ending results equals to 1 as if I did L2-I2. But cell I2 can't greater than L2. If it is it will choose L2 again to equals 1 day. Remember nothing can be greater than L2. Lastly it needs to omit weekends and holidays; I can wait for that.

Here's all my strings I've choose. Yours is close

=MIN(IF(ISNUMBER(I2:K2),IF(I2:K2<L2,I2:K2,L2),L2))

=IF(OR(J2<K2,J2>L2),0,
ABS(NETWORKDAYS(MIN(J2,L2),MAX(I2,K2))
))

=IF(OR(J2<K2,I2>L2),0,(MIN(J2,L2)-MAX(I2,K2)+1))

AND(I2>=K2,J2<=L2)),MAX(0,(J2-I2)-(L2-K2)),
IF(OR(AND(I2<=K2,J2<=L2),AND(I2>=K2,J2>L2)),
MAX(0,(K2-I2))+MAX(0,J2-L2),NA())))
 
Upvote 0
Soryy L2-I2 will equal 0; diregard what I said earlier. But yours is working fine I just need to get the correct date to use to determine my elapsed days.
The very first thing it will do is;

If I2 is lesser than L2 it will determine the difference. If I2 is greater or null than L2 it will choose the losest from J2, or K2 to determine the difference but they still can't be greater than L2.
 
Upvote 0
Sorry L2-I2 will equal 0; disregard what I said earlier. But yours is working fine I just need to get the correct date to use to determine my elapsed days.

The very first thing it will do is;
If I2 is lesser than L2 it will determine the difference from L2-I2. If I2 is greater or null than L2 it can be the same if not it will choose the lowest from J2 or K2 or the same date in L2 to determine the difference but they still can't be greater than L2.; if the cells are greater or null it will choose L2 again. Out of the three cells (I2,J2,K2) dates when the cells are lesser or null than L2 it cannot max no more than 90 days when determining the difference. If they do it will choose L2 again.
Lastly I want to omit weekend and Holidays.
 
Upvote 0
You've added some new conditions but as it sits, my formula picks the lowest date that is before L2, if none of them are it picks L2

Excel Workbook
GHIJKL
1
21/11/20071/14/20071/14/20071/13/2007
3
4
51/11/2007
6
Sheet1
 
Upvote 0
Yea that did work but I discovered that my dates are as 3 to four months old. So now I need it to give me the diff when the first criterion is not met.

This is working a little bit but I need it to choose the max date in the correct cell from J2, IK, still not going over 90 days as from I2. Again if either of these conditions is not met it will choose L2 again.
I got the first condition to work but it is using the min from cells J2:K2 but that too can be over 90 days. Only the max date not going over 90 days.

=IF(L2-I2>90,MIN(J2:K2,L2))

Glad that you can help and patient!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Automatic Date
Replies
5
Views
297
Jlopez0320
J
Z
  • Question
Sum Sales Values by Dates over different periods
Replies
6
Views
436
RobP
R
M
  • Solved
Create sheet for every day of month using Master template, name each sheet as per day of month. VBA
Replies
4
Views
386
John_w
J
S
  • Question
VBA Challenge
Replies
2
Views
449
SnowyBry
S
F
  • Solved
Loaded question with filter function multiple criteria and return value.
Replies
2
Views
217
floW_5
F

Forum statistics

Threads
1,221,596
Messages
6,160,716
Members
451,665
Latest member
PierreF

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