# calculate days360 given an end date

#### almouchie

##### Board Regular
salam everyone
i would like some help : stock of computer bought & sold
column C column d
1/1/05 6/1/05
3/1/05 4/1/05
7/1/05
9/1/05 10/1/05
10/1/05 11/01/05
12/01/05 15/1/05
i would like to calculate the number of days between a date(i specify each time) & the x(i specify how many each time) number of computer that have been here the longest period.
ex. i have sold 3 computers on 10/1/05. i want to calculate number of days 4 each 3 computers between 10/1/05 & ( column d isnot empty) the the oldest 3 computer in my stock
is it any clear?
i would appreciate any hlep
RB

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
almouchie said:
is it any clear?

No.

...so I'll guess:
Book1
CDEFGH
1BoughtSoldNoofcompDays
22005-01-012005-01-0639
32005-01-032005-01-04Date7
42005-01-072005-01-101
52005-01-092005-01-10
62005-01-102005-01-11
72005-01-122005-01-15
Sheet4

DEAR fairwinds
THANK very much it was a big help
when i tried to apply formula in G3 i got #NUM!
any idea why
another does it also work if add more stock of change it
thanks

Re #NUM!# error:
The formula needs to be confirmed with Ctrl + shift + enter, not just with enter. Enter the formula in G2 as explained above, then just drag it down.

To expand the stock you need to expand the ranges in the formulas but apart from that it should work.

sorry i may be dense but its not working
i copied formula & pasted it in G2 but if i press clt+shift+enter all 2gether nothing happens , i tried draggin it b4 still the same problem #NUM!

IF I WERE TO EXPAND DATA WILL I HAVE TO CHANGE THE RANGEJIN FORMULA MANUALLY?

Replicate my layout exactly, however use the date formats that you usually do.

Then copy this:

=IF(ROW()-ROW(\$G\$1)>\$F\$2,"",LARGE((\$F\$4-\$C\$2:\$C\$7)*(\$D\$2:\$D\$7<>""),ROW()-ROW(\$G\$1)))

and paste it in G2. Then change \$C\$7 and \$D\$7 refs in the formula so it covers the maximum dates that you can have. It does not matter if it goes further down than your dates if the number of dates can vary.
Then instead of pressing enter, you press Ctrl + shift + enter simultaneously. If done correctly, the formula will be embraced by curly brackets as you see in my exhibit. Then drag the formula down as far as you need.

tx it worked
but i want computer sold on same day as invoice date to be included & i want computer sold after invoice date not to be included.
make any sense?
ex. consider invoice date is 23/01/05
if computer bought on 23/01/05 & sold 23/01/05
another computer bought on 23/01/05 but sold on 30/01/05
then the first computer will be considered being bought same day as invoice date but second computer will not as it was sold at date later then invoice date.
can i adjust that in the formula
thanks

Maybe like this:

F4:
=MIN(F2,SUMPRODUCT(--(C2:C7<=F6),--(D2:D7<>"")))

G2:
=IF(ROW()-ROW(\$G\$1)>\$F\$4,"",LARGE((\$F\$6-\$C\$2:\$C\$7)*(\$D\$2:\$D\$7<>""),ROW()-ROW(\$G\$1)))
Ctrl + shift + enter and drag down.
Book1
CDEFG
1BoughtSoldNoofcompDays
22005-01-012005-01-0669
32005-01-032005-01-04notoshow7
42005-01-0741
52005-01-092005-01-10Date0
62005-01-102005-01-112005-01-10
72005-01-122005-01-15
Sheet3

it seems to still consider computers that have been sold after the date of invoice
how can i exclude them in the formula
invoice date 23/01/05
computer bought 23/01/05 sold 10/02/05
should not be considered as it was sold after the invoice date
any idea

Yes, I was checking the "bought" dates not the "sold"

F4: =MIN(F2,SUMPRODUCT(--(D2:D7<>""),--(D2:D7<=\$F\$6)))

G2: =IF(ROW()-ROW(\$G\$1)>\$F\$4,"",LARGE((\$F\$6-\$C\$2:\$C\$7)*(\$D\$2:\$D\$7<>"")*(\$D\$2:\$D\$7<=\$F\$6),ROW()-ROW(\$G\$1)))

Replies
4
Views
262
Replies
5
Views
230
Replies
19
Views
610
Replies
9
Views
298
Replies
3
Views
178

1,207,390
Messages
6,078,204
Members
446,321
Latest member
thecachingyeti

### 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.

### Which adblocker are you using?

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

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