Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie: Kalifa la Simpatie.ro
| Femeie 20 ani Ialomita cauta Barbat 19 - 32 ani |
|
TonyTzu
Moderator
Inregistrat: acum 12 ani
Postari: 252
|
|
While it's easy to extract week number from a date, just using =WEEKNUM(DATE), the opposite is a bit more tricky. Thanks to for the following solution:
Note. Please remember that the below formulas work based on the ISO week date system, where the week starts on Monday and the week containing the 1st Thursday of the year is considered week 1. For example, in the year 2016, the first Thursday is January 7, and that is why week 1 begins on 4-Jan-2016.
Start date formula (in BU10 is the week number): =DATE("2018";1;-2) - WEEKDAY(DATE("2018";1;3)) + BU10*7 Instead of 2018 you can add whatever year you want.
This part of the formula: DATE("2018";1;-2) - WEEKDAY(DATE("2018";1;3)) calculates when was the first Monday of the year 2018.
End date formula can be as simple as: Start_date+6. If you want only working days, Monday to Friday, it's Start_date+4.
Example:
Assuming you wrote week number in cell A1 and want start date in cell B1 and end date in cell B2, where end date should be Friday, here's how it's going to look like:
A1=25 B1=DATE("2018";1;-2) - WEEKDAY(DATE("2018";1;3)) + A1*7 B2=B1+4
********
To get a month corresponding to a week number, you can use the following formula: =MONTH(DATE("2018";1;-2) - WEEKDAY(DATE(2018;1;3)) + B2 * 7)
********
The source link also shows Other ways to convert week number to date in Excel. Make sure you check them out.
|
|
pus acum 5 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2182
|
|
Another solution:
Start date: =MAX(DATE($A$1;1;1);DATE($A$1;1;1)-WEEKDAY(DATE($A$1;1;1);2)+(A2-1)*7+1)
End date: =MIN(DATE($A$1+1;1;0);DATE($A$1;1;1)-WEEKDAY(DATE($A$1;1;1);2)+A2*7)
A1 = year A2 = week number
_______________________________________
|
|
pus acum 3 ani |
|