Trusted Members

October 17, 2018

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

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

June 26, 2016

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

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

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

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

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)