| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Convert weeknr to a date
Hi,
Could someone pls help me with finding a good way of converting 05445 to 2005-10-04 ? i.e. converting a date in the format of year+week+daynr to year-month-day. thank you, Sten |
|
#2
|
|||
|
|||
|
Convert weeknr to a date
Hi Sten
One way =DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A1) Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hi, Could someone pls help me with finding a good way of converting 05445 to 2005-10-04 ? i.e. converting a date in the format of year+week+daynr to year-month-day. thank you, Sten |
|
#3
|
|||
|
|||
|
Convert weeknr to a date
Hi Sten
I should have added, Format cell as date in your preferred format. Regards Roger Govier Roger Govier wrote: Hi Sten One way =DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A1) Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hi, Could someone pls help me with finding a good way of converting 05445 to 2005-10-04 ? i.e. converting a date in the format of year+week+daynr to year-month-day. thank you, Sten |
|
#4
|
|||
|
|||
|
Convert weeknr to a date
Hi Roger,
thank you for your answer. However i dont quite understand this will work. A1=05445 DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A1) The equation will give me, 2005 +308 + 5 = 2318 the equation Date(2318) will give me 1906 05 06 If you meant to have commas between then year=2005, month=44, day=5 then that would give me 2008-08-03 not 2005-11-04 what am i missing ? The equation is right for the year and day but not month. so i thought of taking 44*7 , then ans /365 then round it off. but there must be a simplier way. still hoping for the way, Sten "Roger Govier" skrev: Hi Sten I should have added, Format cell as date in your preferred format. Regards Roger Govier Roger Govier wrote: Hi Sten One way =DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A1) Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hi, Could someone pls help me with finding a good way of converting 05445 to 2005-10-04 ? i.e. converting a date in the format of year+week+daynr to year-month-day. thank you, Sten |
|
#5
|
|||
|
|||
|
Convert weeknr to a date
Hi Sten
Did you try it? =DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A1) The first part =DATE(2000+LEFT(A1,2),1,) = DATE((2000+05),1,1) =DATE(2005,1,1) = 01/01/2005 + the second part =MID(A1,3,2)=44 * 7 = 308 + the third part RIGHT(A1) = 5 308 + 5 = 313 =01/01/2005 + 313 = 10/11/2005 or serial date 38666 I cannot see how 05445 could possibly return 2005-10-04 since you said it was equal to year+week+daynumber so I don't see how you were ever expecting it to be October, or the 4th. Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hi Roger, thank you for your answer. However i dont quite understand this will work. A1=05445 DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A1) The equation will give me, 2005 +308 + 5 = 2318 the equation Date(2318) will give me 1906 05 06 If you meant to have commas between then year=2005, month=44, day=5 then that would give me 2008-08-03 not 2005-11-04 what am i missing ? The equation is right for the year and day but not month. so i thought of taking 44*7 , then ans /365 then round it off. but there must be a simplier way. still hoping for the way, Sten "Roger Govier" skrev: Hi Sten I should have added, Format cell as date in your preferred format. Regards Roger Govier Roger Govier wrote: Hi Sten One way =DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT( A1) Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hi, Could someone pls help me with finding a good way of converting 05445 to 2005-10-04 ? i.e. converting a date in the format of year+week+daynr to year-month-day. thank you, Sten |
|
#6
|
|||
|
|||
|
Convert weeknr to a date
Hello Roger,
i woke up and saw the ligth, thanks for your help. Sten Melin "Roger Govier" skrev: Hi Sten Did you try it? =DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A1) The first part =DATE(2000+LEFT(A1,2),1,) = DATE((2000+05),1,1) =DATE(2005,1,1) = 01/01/2005 + the second part =MID(A1,3,2)=44 * 7 = 308 + the third part RIGHT(A1) = 5 308 + 5 = 313 =01/01/2005 + 313 = 10/11/2005 or serial date 38666 I cannot see how 05445 could possibly return 2005-10-04 since you said it was equal to year+week+daynumber so I don't see how you were ever expecting it to be October, or the 4th. Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hi Roger, thank you for your answer. However i dont quite understand this will work. A1=05445 DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A1) The equation will give me, 2005 +308 + 5 = 2318 the equation Date(2318) will give me 1906 05 06 If you meant to have commas between then year=2005, month=44, day=5 then that would give me 2008-08-03 not 2005-11-04 what am i missing ? The equation is right for the year and day but not month. so i thought of taking 44*7 , then ans /365 then round it off. but there must be a simplier way. still hoping for the way, Sten "Roger Govier" skrev: Hi Sten I should have added, Format cell as date in your preferred format. Regards Roger Govier Roger Govier wrote: Hi Sten One way =DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT( A1) Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hi, Could someone pls help me with finding a good way of converting 05445 to 2005-10-04 ? i.e. converting a date in the format of year+week+daynr to year-month-day. thank you, Sten |
|
#7
|
|||
|
|||
|
Convert weeknr to a date
Hi Sten
i woke up and saw the ligth, Often happens<bg. Glad to have been able to provide the candle. Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hello Roger, i woke up and saw the ligth, thanks for your help. Sten Melin "Roger Govier" skrev: Hi Sten Did you try it? =DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A 1) The first part =DATE(2000+LEFT(A1,2),1,) = DATE((2000+05),1,1) =DATE(2005,1,1) = 01/01/2005 + the second part =MID(A1,3,2)=44 * 7 = 308 + the third part RIGHT(A1) = 5 308 + 5 = 313 =01/01/2005 + 313 = 10/11/2005 or serial date 38666 I cannot see how 05445 could possibly return 2005-10-04 since you said it was equal to year+week+daynumber so I don't see how you were ever expecting it to be October, or the 4th. Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hi Roger, thank you for your answer. However i dont quite understand this will work. A1=05445 DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGHT(A 1) The equation will give me, 2005 +308 + 5 = 2318 the equation Date(2318) will give me 1906 05 06 If you meant to have commas between then year=2005, month=44, day=5 then that would give me 2008-08-03 not 2005-11-04 what am i missing ? The equation is right for the year and day but not month. so i thought of taking 44*7 , then ans /365 then round it off. but there must be a simplier way. still hoping for the way, Sten "Roger Govier" skrev: Hi Sten I should have added, Format cell as date in your preferred format. Regards Roger Govier Roger Govier wrote: Hi Sten One way =DATE(2000+LEFT(A1,2),1,1)+MID(A1,3,2)*7+RIGH T(A1) Regards Roger Govier converting 05445 to 2005-10-04 wrote: Hi, Could someone pls help me with finding a good way of converting 05445 to 2005-10-04 ? i.e. converting a date in the format of year+week+daynr to year-month-day. thank you, Sten |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
| How to convert string to a date | Excel Worksheet Functions | |||
| extract numbers, convert to date | Excel Discussion (Misc queries) | |||
| convert julian date to gregorian date | Excel Discussion (Misc queries) | |||
| How do I convert a Julian date into a regular date? | Excel Discussion (Misc queries) | |||
Linear Mode
