Start date and End date in a calendar

elynoy

Board Regular
Joined
Oct 29, 2018
Messages
160
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Hello, I use this formula
Code:
=DATA(M2;D2;1)

and this one too
Code:
=SE(H6<>"";SE(MÊS(H6)<>MÊS(H6+1);"";H6+1);"")

to retrieve the start date and the end date of the month.

How can i make it start on day 25 and go till day 26 of the next month since the formula hides the days if the months has one day less or more like february?

Best regards,
eLy
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I can add a visual effect of what I have and what I need.

I have this:
12345678910111213141516171819202122232425262728293031

<tbody>
</tbody>

Beeing January 1 the beggining of the month and 31 the end of january.

I want it to look like this:

262728293012345678910111213141516171819202122232425

<tbody>
</tbody>

Beeing day 26 the end of december and day 25 the end of January.

best regards,
eLy
 
Upvote 0
Hi, this is a longer version, I am sure there would be a shorter version of this, but this should work:


Book1
ABCDE
4MonthDay 1Day 2Day 3Day 4
577/267/277/287/29
688/268/278/288/29
Sheet3
Cell Formulas
RangeFormula
B5=EOMONTH(DATE(2019,A5,1),-1)+26
C5=IFERROR(IF(AND(MONTH(B5+1)>MONTH($B5),DAY(B5+1)>25),"",B5+1),"")
A6=A5+1
 
Upvote 0
It works like a charm, but now I have another problem. I have the weekends and the holidays with conditional formatting. the weekends seem to be working fine, but the holidays, start to messe when "December January" starts it retrieves the same exact place as november and december but in january it adds one instead of removing i think. This is the formula I use to find the holidays in the date:
Code:
=PROCV(M$447;Feriados;1;0)

when I said it messes up I mean. December has day 1, 8 and 25. And in the next monthly calendar wich is 26 december till 25 January, it adds the holiday conditional formula to the days 31, 07 and 24 (like if it was december and not January. wich should be day 1 as holiday.

Best regards,
eLy
 
Last edited:
Upvote 0
Hi elynoy,

Below should solve for the first issue


Book1
ABCDEFGH
13YearMonthDay 1Day 2Day 3Day 4Day 5Day 6
14201977/267/277/287/297/307/31
15201988/268/278/288/298/308/31
16201999/269/279/289/299/3010/1
1720191010/2610/2710/2810/2910/3010/31
1820191111/2611/2711/2811/2911/3012/1
1920191212/2612/2712/2812/2912/3012/31
Sheet3
Cell Formulas
RangeFormula
C14=EOMONTH(DATE(A14,B14,1),-1)+26
D14=IFERROR(IF(MONTH($C14)=MONTH(C14+1),C14+1,IF(DAY(C14+1)<=25,C14+1,"")),"")


Can you give me a little more details for the holiday? i think you are using VLOOKUP from somewhere. can you give me some sample data.
 
Upvote 0
Nevermind. I forgot to change the procv cell for the conditional formatting. now it's how it's supposed to be wich is, no holiday in it since the year isnt the same.

One more question related to this subject thought. how can I add that holiday? I need to add one year to the sheet somehow since they are automaticaly shown based on the year.


I need to change the year for it to show. Exemple:

January 1 it's a holiday. since it starts at 26 december 2018 and goes to 25 January 2019, the selected year is 2019 so it works fine, but in December/January the year goes from 26 December 2019 to 25 January 2020 and since the base year is 2019 it wont show the january 1 as a holyday.

Best regards,
eLy
 
Last edited:
Upvote 0
maybe:

YearMonth25282930311456781112131415181920212226
2019​
1​
25/01/2019​
28/01/2019​
29/01/2019​
30/01/2019​
31/01/2019​
2019​
2​
25/02/2019​
01/02/2019​
04/02/2019​
05/02/2019​
06/02/2019​
07/02/2019​
08/02/2019​
11/02/2019​
12/02/2019​
13/02/2019​
14/02/2019​
15/02/2019​
18/02/2019​
19/02/2019​
20/02/2019​
21/02/2019​
22/02/2019​
26/02/2019​

with :
- defined start date & end date

Parameter
Value
StartDate
25/01/2019​
EndDate
26/02/2019​

- no holidays (sat, sun and defined)

Holidays
01/01/2019​
01/01/2020​

IMHO your layout isn't good but this is your choice ;)
 
Last edited:
Upvote 0
One more question related to this subject thought. how can I add that holiday? I need to add one year to the sheet somehow since they are automaticaly shown based on the year.


I need to change the year for it to show. Exemple:

January 1 it's a holiday. since it starts at 26 december 2018 and goes to 25 January 2019, the selected year is 2019 so it works fine, but in December/January the year goes from 26 December 2019 to 25 January 2020 and since the base year is 2019 it wont show the january 1 as a holyday.

Best regards,
eLy

You can add the holidays for the next year to the same worksheet, if there are any formula there, then will need some dummy data.
 
Upvote 0
Correction:

YearMonth25/128/129/130/131/11/24/25/26/27/28/211/212/213/214/215/218/219/220/221/222/225/226/2
2019​
1​
25/01/2019​
28/01/2019​
29/01/2019​
30/01/2019​
31/01/2019​
2019​
2​
01/02/2019​
04/02/2019​
05/02/2019​
06/02/2019​
07/02/2019​
08/02/2019​
11/02/2019​
12/02/2019​
13/02/2019​
14/02/2019​
15/02/2019​
18/02/2019​
19/02/2019​
20/02/2019​
21/02/2019​
22/02/2019​
25/02/2019​
26/02/2019​

Would be fine to see your real layout example and some holidays, I mean what you really want to achieve
 
Last edited:
Upvote 0
Aryatec, is it possible to choose the date to start with your formula?

I mean, I asked for it to start at day 26 december and end at 25 January and so on, but is it possible to make it so I can choose when to start?

so If on a cell i have, let's say, day the 26 like i asked wich mean starting from december to january, or, if that same cell has day 1 as startit starts the january 1 instead?

The holydays I got it to work somehow, maybe I messed too much trying to fix it before that I managed to fix it somehow.

best regards,
eLy
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top