Conversion of dates

[Vjee]

Hello all,

I need to convert dates into periods in an excel sheet.

For example 1/1/2007 -> 28/1/2007 = P1
29/1/2007 -> 25/2/2007 = P2
....until P13 (13 periods of 4 weeks). So a period does not necessarily
match the number of the month. (eg. 20/12/2007 = P13)

If a cell for example says 10/1/2007 "P1" should be returned, if
25/12/2007 -> "P13", ...

I tried things like "=IF(1/1/2007<A1<28/1/2007; P1, ...)" but that
doesn't work.

thanks for your advice on this one !

Xavier

 

[Mike H]

One possible solution is with a table like this in my case in A1:B3

01/01/2007 P1
29/01/2007 P2
26/02/2007 P3

Note that the dates must be sorted ascending and are your transition dates
from 1 period to the next.
Then a formula
=VLOOKUP(C1,A1:B3,2,TRUE)
Where C1 is the date you want to convert

Mike

 

[Stephen]

Make yourself a table with start dates for periods in the first column and
return values in the second. The dates should be in ascending order. Then
use VLOOKUP with the fourth parameter (range_lookup) as TRUE. (From Help:
"If TRUE or omitted, an approximate match is returned. In other words, if an
exact match is not found, the next largest value that is less than
lookup_value is returned.")

 

[Vjee]

Works perfect !!

Thanks guys !