Start date and End date in a calendar

E

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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
I can add a visual effect of what I have and what I need.

I have this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[/TR]
</tbody>[/TABLE]

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

I want it to look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Year[/td][td=bgcolor:#70AD47]Month[/td][td=bgcolor:#70AD47]25[/td][td=bgcolor:#70AD47]28[/td][td=bgcolor:#70AD47]29[/td][td=bgcolor:#70AD47]30[/td][td=bgcolor:#70AD47]31[/td][td=bgcolor:#70AD47]1[/td][td=bgcolor:#70AD47]4[/td][td=bgcolor:#70AD47]5[/td][td=bgcolor:#70AD47]6[/td][td=bgcolor:#70AD47]7[/td][td=bgcolor:#70AD47]8[/td][td=bgcolor:#70AD47]11[/td][td=bgcolor:#70AD47]12[/td][td=bgcolor:#70AD47]13[/td][td=bgcolor:#70AD47]14[/td][td=bgcolor:#70AD47]15[/td][td=bgcolor:#70AD47]18[/td][td=bgcolor:#70AD47]19[/td][td=bgcolor:#70AD47]20[/td][td=bgcolor:#70AD47]21[/td][td=bgcolor:#70AD47]22[/td][td=bgcolor:#70AD47]26[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
2019​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
25/01/2019​
[/td][td=bgcolor:#E2EFDA]
28/01/2019​
[/td][td=bgcolor:#E2EFDA]
29/01/2019​
[/td][td=bgcolor:#E2EFDA]
30/01/2019​
[/td][td=bgcolor:#E2EFDA]
31/01/2019​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2019​
[/td][td]
2​
[/td][td]
25/02/2019​
[/td][td][/td][td][/td][td][/td][td][/td][td]
01/02/2019​
[/td][td]
04/02/2019​
[/td][td]
05/02/2019​
[/td][td]
06/02/2019​
[/td][td]
07/02/2019​
[/td][td]
08/02/2019​
[/td][td]
11/02/2019​
[/td][td]
12/02/2019​
[/td][td]
13/02/2019​
[/td][td]
14/02/2019​
[/td][td]
15/02/2019​
[/td][td]
18/02/2019​
[/td][td]
19/02/2019​
[/td][td]
20/02/2019​
[/td][td]
21/02/2019​
[/td][td]
22/02/2019​
[/td][td]
26/02/2019​
[/td][/tr]
[/table]

with :
- defined start date & end date

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]
Parameter​
[/td][td=bgcolor:#5B9BD5]
Value​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]StartDate[/td][td=bgcolor:#DDEBF7]
25/01/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]EndDate[/td][td]
26/02/2019​
[/td][/tr]
[/table]

- no holidays (sat, sun and defined)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Holidays[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
01/01/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
01/01/2020​
[/td][/tr]
[/table]

IMHO your layout isn't good but this is your choice ;)
 
Last edited:
Upvote 0
elynoy said:
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
Click to expand...

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:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Year[/td][td=bgcolor:#70AD47]Month[/td][td=bgcolor:#70AD47]25/1[/td][td=bgcolor:#70AD47]28/1[/td][td=bgcolor:#70AD47]29/1[/td][td=bgcolor:#70AD47]30/1[/td][td=bgcolor:#70AD47]31/1[/td][td=bgcolor:#70AD47]1/2[/td][td=bgcolor:#70AD47]4/2[/td][td=bgcolor:#70AD47]5/2[/td][td=bgcolor:#70AD47]6/2[/td][td=bgcolor:#70AD47]7/2[/td][td=bgcolor:#70AD47]8/2[/td][td=bgcolor:#70AD47]11/2[/td][td=bgcolor:#70AD47]12/2[/td][td=bgcolor:#70AD47]13/2[/td][td=bgcolor:#70AD47]14/2[/td][td=bgcolor:#70AD47]15/2[/td][td=bgcolor:#70AD47]18/2[/td][td=bgcolor:#70AD47]19/2[/td][td=bgcolor:#70AD47]20/2[/td][td=bgcolor:#70AD47]21/2[/td][td=bgcolor:#70AD47]22/2[/td][td=bgcolor:#70AD47]25/2[/td][td=bgcolor:#70AD47]26/2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
2019​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
25/01/2019​
[/td][td=bgcolor:#E2EFDA]
28/01/2019​
[/td][td=bgcolor:#E2EFDA]
29/01/2019​
[/td][td=bgcolor:#E2EFDA]
30/01/2019​
[/td][td=bgcolor:#E2EFDA]
31/01/2019​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2019​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
01/02/2019​
[/td][td]
04/02/2019​
[/td][td]
05/02/2019​
[/td][td]
06/02/2019​
[/td][td]
07/02/2019​
[/td][td]
08/02/2019​
[/td][td]
11/02/2019​
[/td][td]
12/02/2019​
[/td][td]
13/02/2019​
[/td][td]
14/02/2019​
[/td][td]
15/02/2019​
[/td][td]
18/02/2019​
[/td][td]
19/02/2019​
[/td][td]
20/02/2019​
[/td][td]
21/02/2019​
[/td][td]
22/02/2019​
[/td][td]
25/02/2019​
[/td][td]
26/02/2019​
[/td][/tr]
[/table]

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
You must log in or register to reply here.

Similar threads

B
  • Solved
Macro code changing date format
Replies
6
Views
636
Beneindias
B
Z
  • Solved
add number of working days to a specific date (Start Date) to have the (End Date)
Replies
5
Views
305
Peter_SSs
Peter_SSs
J
  • Solved
Sum FTEs based on start/end date
Replies
3
Views
712
etaf
etaf
H
  • Question Question
Date from CSV file not copying & pasting correctly. Day and Month are reversed.
Replies
1
Views
168
jkpieterse
jkpieterse
B
  • Question Question
calculate monthly value
Replies
0
Views
239
BrutalLogiC
B

Forum statistics

Threads
1,223,956
Messages
6,175,614
Members
452,661
Latest member
Nonhle

Share this page

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