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.
|
Nou pe simpatie:
bella_sexy
 | Femeie
25 ani
Bucuresti
cauta Barbat
27 - 48 ani |
|
|
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| #1 |
TRaPModerator
Postari: 960
|
|
You want to get the date interval of each month, using a date in a column or just the number of the month in the year.
A1:A12 = various dates, 1 from each month
B1:B12 = numbers 1 to 12, corresponding to the months.
C1:C12 = FIRST DAY OF THE MONTH
D1:D12 = LAST DAY OF THE MONTH
C1 =DATE(YEAR(TODAY());B1;1)
If you work with the dates in column A instead of the numbers in B, you can use the following formula in C1 instead:
C1 =EOMONTH(A1;-1)+1
Now for the last day of the month you use the first day of the month:
D1 =EOMONTH(C1;0)
or the date in A1:
D1 =EOMONTH(A1;0)
Source:
https://exceljet.net/formula/get-last-day-of-month
|
|
| |
|
| #2 |
|
|
If you want, for example, to just extract the number of the last day of the month, and not the date:
eg. for February to display just 28 instead of 28.02.2023
Assuming that in cell X2 I have the first day of the month, eg. 01.02.2023
=DAY(EOMONTH(X2;0))
will return just the number 28.
_______________________________________
|
|
| |
|
| #3 |
TRaPModerator
Postari: 960
|
|
If you have a dropdown with years - say in A1 - and want first day of the year, and last day of the year, do this:
First day of the year:
=DATE(A1;1;1)
Last day of the year:
=DATE(A1;12;31)
For last day, if you are planning to extract the day of the last finished month from a column where you have daily entries, you can use the following formula:
=MAXIFS($L$38:$L$5000; $L$38:$L$5000;">"&DATE(P1;1;1); $L$38:$L$5000;"<"&DATE(P1;12;31))
For example at the date of this post - 07.05.2024, last finished month would be April, so the formula above would show 30.04.2024, regardless of when the entry was entered in April.
If the newest date of an entry was in March, it would return March.
|
|
| |
|
