General numbers lookup from FY-PP-WK-D resulting in MM-DD-YY

I have 8 numbers in general format in the J column of the main! sheet:
20100416
20100527
20100427
20100513
20100414

These numbers represent dates as follows:
YYYY ( Fiscal year)-Pay Period ( 01 thru 26)-(week of pay period, either 1
or 2), and day of week; 1=sat, 2=sun,3=Mon,4=Tue,5=Wed,6=Thu & 7 = Fri.
The first number 2010 04 1 6 would indicate Fiscal year 2010, PP 04, week 1,
day 6.

I also have another sheet (table!) with first 7 of the 8 numbers that
indicates the first day of the week based on the 042, 051, 052, etc, such as
below with the 042 representing the beginning of the week, Feb 6. ( 051 would
represent Feb 13 , 052 would represent Feb 20, etc.)

2010042 6-Feb
The above Feb 6 result is in table!F29

I would like some sort of lookup that would produce the actal dates as shown
below MM-DD-YY, and would like the result in the V column of main!
J V
20100416 1/4/10
20100527 2/26/10
20100427 2/12/10
20100513 2/15/10
20100414 2/1/10

I hope I explained this correctly.

Much thanks,

Steve

Hi Steve,

If I understand you correctly, you need something like this in column V:

=VLOOKUP(VALUE(LEFT($J1,7)),table!$E$1:$F$50,2,0)+ RIGHT($J1,1)-1

However, the first and last dates given in your example results table do not
correspond...



"Steve" wrote in message
...
I have 8 numbers in general format in the J column of the main! sheet:
20100416
20100527
20100427
20100513
20100414

These numbers represent dates as follows:
YYYY ( Fiscal year)-Pay Period ( 01 thru 26)-(week of pay period, either 1
or 2), and day of week; 1=sat, 2=sun,3=Mon,4=Tue,5=Wed,6=Thu & 7 = Fri.
The first number 2010 04 1 6 would indicate Fiscal year 2010, PP 04, week
1,
day 6.

I also have another sheet (table!) with first 7 of the 8 numbers that
indicates the first day of the week based on the 042, 051, 052, etc, such
as
below with the 042 representing the beginning of the week, Feb 6. ( 051
would
represent Feb 13 , 052 would represent Feb 20, etc.)

2010042 6-Feb
The above Feb 6 result is in table!F29

I would like some sort of lookup that would produce the actal dates as
shown
below MM-DD-YY, and would like the result in the V column of main!
J V
20100416 1/4/10
20100527 2/26/10
20100427 2/12/10
20100513 2/15/10
20100414 2/1/10

I hope I explained this correctly.

Much thanks,

Steve

Perfect...and you're right about those 2 dates. I did have them wrong.

Thank you very much.

Steve

"Steve Dunn" wrote:

Hi Steve,

If I understand you correctly, you need something like this in column V:

=VLOOKUP(VALUE(LEFT($J1,7)),table!$E$1:$F$50,2,0)+ RIGHT($J1,1)-1

However, the first and last dates given in your example results table do not
correspond...



"Steve" wrote in message
...
I have 8 numbers in general format in the J column of the main! sheet:
20100416
20100527
20100427
20100513
20100414

These numbers represent dates as follows:
YYYY ( Fiscal year)-Pay Period ( 01 thru 26)-(week of pay period, either 1
or 2), and day of week; 1=sat, 2=sun,3=Mon,4=Tue,5=Wed,6=Thu & 7 = Fri.
The first number 2010 04 1 6 would indicate Fiscal year 2010, PP 04, week
1,
day 6.

I also have another sheet (table!) with first 7 of the 8 numbers that
indicates the first day of the week based on the 042, 051, 052, etc, such
as
below with the 042 representing the beginning of the week, Feb 6. ( 051
would
represent Feb 13 , 052 would represent Feb 20, etc.)

2010042 6-Feb
The above Feb 6 result is in table!F29

I would like some sort of lookup that would produce the actal dates as
shown
below MM-DD-YY, and would like the result in the V column of main!
J V
20100416 1/4/10
20100527 2/26/10
20100427 2/12/10
20100513 2/15/10
20100414 2/1/10

I hope I explained this correctly.

Much thanks,

Steve

You're welcome, glad to help.