Prev: changing formula
Next: Conditional Formatting based on match to external list
From: Steve on 13 Apr 2010 16:28
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

From: Steve Dunn on 14 Apr 2010 06:17
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" <Steve(a)discussions.microsoft.com> wrote in message
news:0DBE636F-F95D-466D-9848-F86374944B11(a)microsoft.com...
>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
>

From: Steve on 14 Apr 2010 10:40
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" <Steve(a)discussions.microsoft.com> wrote in message
> news:0DBE636F-F95D-466D-9848-F86374944B11(a)microsoft.com...
> >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
> >
>
From: Steve Dunn on 14 Apr 2010 10:46
You're welcome, glad to help.
 | 
Pages: 1
Prev: changing formula
Next: Conditional Formatting based on match to external list