|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 10:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com