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
Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] Extract Start Date and End Date (or Month) from Week Number Moderat de TRaP, TonyTzu
Autor
Mesaj Pagini: 1
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
   
Pagini: 1  

Mergi la