Hi,
I have a datefilter: 06/01/2013..15/02/2013
Then I have colums for each month:
Column A: 01/01/2013..31/01/2013
Column B: 01/02/2013..28/02/2013
etc etc
Now I want to know how many days there are in the datefilter for column A (this should be 25) and how many in column B (this should be 15)
I need to calculate how many days for each month that are in the filter…
Does anyone have an idea?
Thanks,
Jef
Date
Votes
9 comments
-
Jet Reports Historic Posts Official comment Hi Jef,
If I understand you correctly, then the answer is not as simple as we might think it would be. It would require finding out the outer boundaries for the date range, and then make calculation for every possible case.
Here I attach the revised worksheet for you to study, look for the solution in cells with green background. Hopefully this is what you're looking for, and can help you with your project.
Andy -
Jet Reports Historic Posts Hi Jef,
I don't understand how you get to 25 and 15 for those 2 months. If you're talking about absolute days, there are 31 in A and 28 in B. If you are talking about weekdays, there are 23 in A and 20 in B. How are you getting 25 and 15?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
It have to be absolute days.
The question is, how many of those 31 days are in the filter 06/01/13..31/01/13
And how many days of those 28 are in the filter 01/02/13..15/02/13
I have attached my workspace file. The yellow/red cells have to provide the formula to calculate this.
The whole story: I will check my inventory about the inbound and outbound of an item.
Then I have to calculate for each month how many days that item was in my inventory.
No kidding, but 13 hours spend so far to find a solution…. :o -
Jet Reports Historic Posts Okay so I'm looking at the yellow cells and I see where you subtract to find out the number of days. If you want to know the actual number of days in the filter, you might have to do your subtractraction and then add 1 since the filter includes both the first and the last day in the range. So the number of days in the filter 06/01/2012..31/01/2012 is actually 26 (31 - 6 + 1) and the number of days in 15/02/2012..29/02/2012 is actually 15 (29 - 16 + 1). Does that help? If not, what is the actual problem?
Regards,
Hughes -
Jet Reports Historic Posts Do you know an JET (or Excel) function that calculates this?
I've got a range of dates ins and outs (at this moment 5000 I think). These are my rows
Than I have columns for each month and then I need a function that calculates how many days of the row data that are in the month dates (columns) -
Jet Reports Historic Posts Jef,
So for each row and range column, you want to know whether the Date In and/or Date Out fall within that month range or not, is that right? You could just use something like this:=IF(AND($S14<=Y$9,$S14>=Y$8),1,0)
Then you can sum up the results for each date column, and you'll know how many of your rows had a date in or date out within the range. Does that help? I'm still not positive I understand, but maybe this gets you closer.
Regards,
Hughes -
Jet Reports Historic Posts Hi Andy,
THIS is it, thank you so much.
initially I thought that there was a JET function that could do this trick.
Again, thanks,
Jef -
Jet Reports Historic Posts Andy,
Small follow-up question: how do you type formulas like this in Excel?
I mean new lines for every part of the formula. I looked with some collegues at this and nobody knows how… :-)
Jef -
Jet Reports Historic Posts Hi Jef,
It's easy, just hit ALT+Enter at the end of every line ;)
Glad to help with your project
Andy
Please sign in to leave a comment.