Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conversion of dates
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conversion of dates
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 "Vjee" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conversion of dates
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conversion of dates
Works perfect !!
Thanks guys ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Day Conversion | Excel Worksheet Functions | |||
CONVERSION | Excel Discussion (Misc queries) | |||
Conversion | Excel Discussion (Misc queries) | |||
Between conversion | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |