Finding oldest date

B

Bob White

Board Regular
Joined
May 4, 2008
Messages
61
<TABLE style="WIDTH: 710pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=948 border=0><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 134pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=179 height=20>date</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=68>1/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=68>2/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=68>3/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=68>4/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=68>5/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=68>6/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=68>7/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=68>8/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=75>9/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=75>10/Jan</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=75>11/Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>quantity </TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>50</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>20</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>23</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>10</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>89</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>48</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>20</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>25</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>35</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>current stock </TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">200</TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>oldest date of manufacture</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">?</TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD><TD style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8"></TD></TR></TBODY></TABLE>


I have a report that lists the quantity of an item that is manufactured on particular day. On some days there will be no items produced. The report is similar to the above table. I don't want to change the table format.

I know the current stock of that item (in this example it is 200). Note that the current stock is not just the sum of the quantiy for each day's production.

I need a function that will determine the oldest date of manufacture of material in the current stock. The function assumes that that the current stock is made up of the most recently produced material. ie the stock is depleted on a first in -first out basis. Essentially the calculation has to sum backwards from the most recent manufacture date until the total = 200 and then lookup to corresponding date.

In the above table the oldest prodcution date would be 6/Jan. The answer to the funstion in example = 6/Jan

Can anyone help with the required calculation? <!-- / message -->
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
I tried and couldn't figure out how to do this with a (non-huge) formula. Here's a UDF that will do the trick, though:

Code: 
Function LatestDate(SoughtCount As Variant, DateRange As Range, QuantityRange As Range)

Dim i As Long, lngSum As Long

For i = QuantityRange.Cells.Count To 1 Step -1
    lngSum = lngSum + QuantityRange.Cells(i)
    If lngSum > SoughtCount Then
        LatestDate = DateRange(i)
        Exit Function
    End If
Next

LatestDate = "#N/A"

End Function
...you can put it into a new module in your workbook (alt+F11, alt+i, m, then paste this in) and use it like this:

Excel Workbook
ABCDEFGHIJKL
1date1-Jan2-Jan3-Jan4-Jan5-Jan6-Jan7-Jan8-Jan9-Jan10-Jan11-Jan
2quantity502023108948202535
3
4current stock200
5oldest date of manufacture1/6
1
Excel 2000
Cell Formulas
RangeFormula
B5=latestdate(B4,B1:L1,B2:L2)


...the first argument can be a range (e.g., B4) or a number (e.g., 200). The other two arguments have to be corresponding ranges.
 
Last edited:
Upvote 0
try this


Excel Workbook
ABCDEFGHIJKL
1date01-Jan02-Jan03-Jan04-Jan05-Jan06-Jan07-Jan08-Jan09-Jan10-Jan11-Jan
2quantity502023108948202535
3320270250227217217128128806035
4
5
6current stock100
7oldest date of manufacture08-Jan
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B3=SUM(B2:$L2)
C3=SUM(C2:$L2)
D3=SUM(D2:$L2)
E3=SUM(E2:$L2)
F3=SUM(F2:$L2)
G3=SUM(G2:$L2)
H3=SUM(H2:$L2)
I3=SUM(I2:$L2)
J3=SUM(J2:$L2)
K3=SUM(K2:$L2)
L3=SUM(L2:$L2)
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
 
Upvote 0
sanrv1f said:
try this
...
=INDEX($B$1:$L$1,COLUMNS(B2:L2)-MATCH(B6-0.00000000001,SUBTOTAL(9,OFFSET(B2,,COLUMNS(B2:L2)-1,,-COLUMN(B2:L2)+COLUMN(B2)-1)),1))
...
Click to expand...

I think that needs to be a bit more elaborate...

Control+shift+enter...

=INDEX($B$1:$L$1,COLUMNS(B2:L2)-MATCH(MIN(ABS(B6-SUBTOTAL(9,OFFSET(B2,,COLUMNS(B2:L2)-1,,-COLUMN(B2:L2)+COLUMN(B2)-1)))),ABS(B6-SUBTOTAL(9,OFFSET(B2,,COLUMNS(B2:L2)-1,,-COLUMN(B2:L2)+COLUMN(B2)-1))),0)+1)

Assigning a name to the subtotal bit would shorten and speed up the formula.
 
Upvote 0
expanding on Aladin's idea,
Excel Workbook
ABCDEFGHIJKL
1sheet201-Jan02-Jan03-Jan04-Jan05-Jan06-Jan07-Jan08-Jan09-Jan10-Jan11-Jan
2quantity502023108948202535
3
4
5
6current stock100
7oldest date of manufacture08-Jan
Sheet1
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.


the above formula would fail if the stock in hand exceeds the sum of all the days quantity (hope it never happens, in theory atleast)


Aladin,

That suggestion gives the nearest match (higher or lower), If I understood correctly, the OP needs the higher value
 
Last edited:
Upvote 0
Thank you for your help on my post.

I have tested you answer and it works perfectly. However if I extend the date range to column Z I cannot adjust the formula to work over an bigger range of column.

Can you explain how to modify the formula please.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
<TABLE style="WIDTH: 1155pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1540 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" span=22 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>1-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 51pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=68>2-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>3-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>4-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>5-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>6-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>7-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>8-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>9-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>10-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>11-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>12-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>13-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>14-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>15-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>16-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>17-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>18-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>19-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>20-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>21-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>22-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>23-Jan</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>24-Jan</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>quantity</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 51pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=68>50</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>20</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>23</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>10</TD><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>89</TD><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>48</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>20</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>25</TD><TD class=xl66 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>35</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1</TD></TR></TBODY></TABLE>
 
Upvote 0
ooops, there was an error in my formula, try this
Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
101-Jan02-Jan03-Jan04-Jan05-Jan06-Jan07-Jan08-Jan09-Jan10-Jan11-Jan12-Jan13-Jan14-Jan15-Jan16-Jan17-Jan18-Jan19-Jan20-Jan21-Jan22-Jan23-Jan24-Jan
2quantity5020231089482025351
3
4
5
6200
707-Jan
Sheet2
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
Another way?

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
11-Jan2-Jan3-Jan4-Jan5-Jan6-Jan7-Jan8-Jan9-Jan10-Jan11-Jan12-Jan13-Jan14-Jan15-Jan16-Jan17-Jan18-Jan19-Jan20-Jan21-Jan22-Jan23-Jan24-Jan
24050202310*89*48202535***********1
3************************
4************************
5************************
6*200**********************
7*7-Jan**********************
Sheet1
 
Upvote 0
One more.....along the same lines as Sankar's approach

=MAX(IF(SUBTOTAL(9,OFFSET(X2,,,,COLUMN(B2:X2)-COLUMNS(B2:X2)-COLUMN(B2)))>=B6,B$1:X$1))
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
817
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
345
esben
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
V
  • Question
Issue with scraping with VBA
Replies
1
Views
490
John_w
J

Forum statistics

Threads
1,214,646
Messages
6,120,715
Members
448,985
Latest member
chocbudda

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