• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

How to Calculate Mother's Day date with VBA in excel formula ?|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / How to Calculate Mother's Day date with VBA in excel formula ?|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHow to Calculate Mother's Day date …
sp_PrintTopic sp_TopicIcon
How to Calculate Mother's Day date with VBA in excel formula ?
Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
1
July 26, 2023 - 2:56 am
sp_Permalink sp_Print

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 ?

Avatar
Hans Hallebeek
the Netherlands
Member
Members


Trusted Members
Level 0
Forum Posts: 182
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
2
July 26, 2023 - 6:32 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
3
July 26, 2023 - 11:25 pm
sp_Permalink sp_Print

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

Avatar
Hans Hallebeek
the Netherlands
Member
Members


Trusted Members
Level 0
Forum Posts: 182
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
4
July 27, 2023 - 6:43 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
5
July 29, 2023 - 2:20 am
sp_Permalink sp_Print

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

Avatar
Hans Hallebeek
the Netherlands
Member
Members


Trusted Members
Level 0
Forum Posts: 182
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
6
July 29, 2023 - 4:01 pm
sp_Permalink sp_Print

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 Smile

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
7
August 1, 2023 - 2:35 am
sp_Permalink sp_Print

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

Avatar
A.Maurizio
Member
Members
Level 0
Forum Posts: 213
Member Since:
June 26, 2016
sp_UserOfflineSmall Offline
8
August 1, 2023 - 3:59 am
sp_Permalink sp_Print

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

Avatar
Hans Hallebeek
the Netherlands
Member
Members


Trusted Members
Level 0
Forum Posts: 182
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
9
August 1, 2023 - 4:20 pm
sp_Permalink sp_Print sp_EditHistory

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Charlotte Holloway, Lorna Henning, TEJA P, Shoua Lee, Mohamed Touahria
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
David von Kleek
Ronald White
Ginette Guevremont
Taryn Ambrosi
Mark Davenport
Christy Nichols
Harald Endres
Ashley Hughes
Herbie Key
Trevor Pindling
Forum Stats:
Groups: 3
Forums: 24
Topics: 6528
Posts: 28594

 

Member Stats:
Guest Posters: 49
Members: 32817
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.