Log In
Register
Home
June 26, 2016
Offline
Hello everyone from Maurizio
My problem is this : I would like to know how to calculate the date of mother's day with the VBA I have an excel formula ?
Trusted Members
October 17, 2018
Offline
Ciao Maurizio,
Take a look here.
Mother's day laike many other datesis a variable date, check this link
How to calculate dates for holidays - Microsoft: Office FAQ - Tek-Tips
June 26, 2016
Offline
Hi Hans Hallebeek Thank you for your link you sent me
But I tried to do everything that is written word for word
But nothing works , despite bringing all the appropriate changes
As I use an excel sheet with office 2019
That's all !
Now I will definitely try other avenues Thanks
greetings and good day to all from A.Maurizio
Trusted Members
October 17, 2018
Offline
I understand your problem but, it works.
It's a question of reading carefully to understand what is explaind in the link
Here's a file with the formula implemented for Mother's Day, second Sunday in May for any year
June 26, 2016
Offline
Hello Hans Hallebeek
Thank you for your file that you sent me
Now I understand that my mistake was to write (WeekDay ) instead of (Day.Week)
Thank you very much
Even if as I told you last time
I had solved this problem by writing this VBA formula:
Function CalculateMother's Day(Year As Integer) As Date
Dim May Day As Date
Dim PrimaDomenica As Date
Dim FestivitaDellaMamma As Date
' Find May 1st of the specified year
May Day = DateSerial(Year, 5, 1)
' Find the weekday of May 1st (0 = Sunday, 1 = Monday, ..., 6 = Saturday)
DimWeekday As Integer
Weekday = Weekday(May Day, vbSunday)
' Calculate how many days to add to arrive at the first Sunday of May
Dim DaysToAdd As Integer
DaysToAdd = IIf(Weekday = 1, 7, 7 - Weekday + 1)
' Find the date of the first Sunday in May
First Sunday = First May + DaysToAdd
' Add 7 days to get Mother's Day date (second Sunday in May)
Mother's Day = First Sunday + 7
CalculateMother's Day = Mother's Day
End Function
That's all .
Anyway Thank you Infinite You are Fantastic too,
Greetings and Happy Weekend from A.Maurizio
Trusted Members
October 17, 2018
Offline
Good to hear,
Yes I have the VBA code for any variable day and more
' We can generalize this to holidays that are defined as the Nth Day of some month,
' such as Martin Luther King's birthday, celebrated on the 3rd Monday of January.
' The following function will return the Nth DayOfWeek for a given month and year:
'SYntax NDOW:
' y = Year
' M = Month
' N = Nth day of M month
' DOW = Day of the week: 1 = Sunday, 2= Monday, etc.
Public Function NDow(y As Integer, M As Integer, N As Integer, DOW As Integer) As Date
NDow = DateSerial(y, M, (8 - WeekDay(DateSerial(y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))
End Function
' To compute this date, we first need a function to tell us how many Mondays there are in the month.
Public Function DOWsInMonth(yr As Integer, M As Integer, DOW As Integer) As Integer
On Error GoTo endFunction
Dim i As Integer
Dim Lim As Integer
Lim = Day(DateSerial(yr, M + 1, 0))
DOWsInMonth = 0
For i = 1 To Lim
If WeekDay(DateSerial(yr, M, i)) = DOW Then
DOWsInMonth = DOWsInMonth + 1
End If
Next i
Exit Function
endFunction:
DOWsInMonth = 0
End Function
Happy coding 
June 26, 2016
Offline
Hello Hans Hallebeek Too Good
And above all Too Good
Tried it and it's beautiful
Thank you very much you are really a friend.
(P.s) If I may dare of your availability
Could you solve this problem for me, which is this:
I want to enter the names that make up the Week
But instead of getting the classic format like : (Monday, Tuesday, Wednesday) etc...!
I also have only (Mon,Tue,Wed) etc...!
I just want to get the single initial letter, such as (L,M,M,G) etc...!
Now on a book that deals with VBA in general I found this formula
( =LEFT(TEXT(C1;"ddd");1) ) which works fine.
But since I should combine it with a Date of the type =DATA(M1;AI2;1) D0ve in Cell (AI2) there is only the number (1) of the month of January
Therefore I was wondering, if it was not possible in the formula (Left(Text also the (=DATE(M1;AI2;1) etc...!
I've tried everything and never succeeded
Could you give me some suggestions Thanks Infinite From A.Maurizio
June 26, 2016
Offline
Hello Hans Hallebeek
Please do not keep my last request in mind any longer
Because by dint of trying I managed to make everything work the way I wanted
Simply writing it like this ( =LEFT(TEXT(DATE(M1;AI2;1);"ddd");1) )
Therefore right I was wrong that it is now working as I wanted
I apologize
Hello - Thanks and Good Evening from A.Maurizio
Trusted Members
October 17, 2018
Offline
See, when you try a little longe you get it.
one more thing, if you want to make sure that the date text is your own language do the following:
( =LEFT(TEXT(DATE(M1;AI2;1);"[$-0010]ddd");1) )
| martedì 1 agosto, 2023 {08:18} |
( =LEFT(TEXT(DATE(M1;AI2;1);"ddd");1) )
| Tuesday 1 August, 2023 {08:18} |
You can read all about language settings here https://msdn.microsoft.com/en-us/library/cc233982.aspx
1 Guest(s)
