Extract data from strings and count function

[rize1159]

I have different production codes e.g in cells A1 to A23, L11260J31, L11261J21 etc. which I want to convert to date by extracting these digits "261". the digit "261" means 18 sep 2011 as this is day of year. Can somebody help

My second question is that I have dates in colunm A like 6/11/2011 i.e mm,dd,yyyy. Now in column B & C, I have production of say product X& Y. This production may be for one day or two or three days like below:

column A column B column C
date product X product Y
6/11/2011 200 0
6/12/2011 10 134
6/13/2011 0 0
6/14/2011 150 0

Result required is count function such that column B gives result of 2 and column C gives count of one. this is because product X has continuous run for consecutive two days which mean one production run. and product Y has one run for one day. End conclusion is if we have data in consecutive rows, count function should result in one (1). and if there is data for one cell it should result in one.

 

[beedistinct]

Please try this for the first question:

=DATE(2011,MONTH(TEXT(MID(F8,4,3),"mm/dd/yy")),DAY(TEXT(MID(F8,4,3),"mm/dd/yy")))

I've assumed that the cell F8 contains your string that contains day of the year.

 

[rize1159]

It results in 9/17/2011 instead of 9/18/2011.

 

[mikerickson]

This should answer part 1:
=DATE(2011, 1, MID(A1,4,3))

For part 2:
The formula =SUMPRODUCT((B1:B12=0)*(B2:B13<>0)) + ((B1<>0)*(B2<>0)) will return the value 2.

and =SUMPRODUCT((C1:C12=0)*(C2:C13<>0)) + ((C1<>0)*(C2<>0)) will return 1.

Note that both B13 and C13 should be empty (or contain 0).

 

[rize1159]

This should answer part 1:
=DATE(2011, 1, MID(A1,4,3))

For part 2:
The formula =SUMPRODUCT((B1:B12=0)*(B2:B13<>0)) + ((B1<>0)*(B2<>0)) will return the value 2.

and =SUMPRODUCT((C1:C12=0)*(C2:C13<>0)) + ((C1<>0)*(C2<>0)) will return 1.

Note that both B13 and C13 should be empty (or contain 0).

thnaks for heping

 

[beedistinct]

This should answer part 1:
=DATE(2011, 1, MID(A1,4,3))

For part 2:
The formula =SUMPRODUCT((B1:B12=0)*(B2:B13<>0)) + ((B1<>0)*(B2<>0)) will return the value 2.

and =SUMPRODUCT((C1:C12=0)*(C2:C13<>0)) + ((C1<>0)*(C2<>0)) will return 1.

Note that both B13 and C13 should be empty (or contain 0).

I was just trying to understand the formula. It doesn't seem to work with the following dataset from B1 to B13. It is returning a value of 3 instead of 2. Am I miising something?

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=64 align=right>91</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>229</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>127</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>23</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>225</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>59</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>0</TD></TR></TBODY></TABLE>

 

[mikerickson]

The formula assumes a header in B1, if there is data in B1.
Try this formula =SUMPRODUCT((B1:B12=0)*(B2:B13<>0))+((B1<>0))
It returns the result 4

91
229
127, 23
225, 59

if you want to ignore a run that starts in the first row, then =SUMPRODUCT((B1:B12=0)*(B2:B13<>0))

 

[beedistinct]

The formula assumes a header in B1, if there is data in B1.
Try this formula =SUMPRODUCT((B1:B12=0)*(B2:B13<>0))+((B1<>0))
It returns the result 4

91
229
127, 23
225, 59

if you want to ignore a run that starts in the first row, then =SUMPRODUCT((B1:B12=0)*(B2:B13<>0))

Thank you so much! It was very helpful!