Log In
Register
Home
June 26, 2016
Offline
Hello everyone from Maurizio
My new problem is this:
With the help of Excel and its VBA I am creating a calendar entirely driven by VBA.
So far everything is ok, but now a desire has arisen that is creating quite a few problems for me which is this:
If you notice in Row (A1: G1) To find and change the Days of the Week I wrote the formula "=Date(M2;M4;1)" as these cells show the year and month of a presumed inserted date in cell (M1)
After that I continue with the other claws simply using the formula (A1+1) etc....!
Then formatting them in ("gggg") I get the writing (veberdi); (Saturday) etc....!
Now if I wanted to get the first letter of the Day of the Week in a big way what would I have to do to achieve all this
While continuing to keep in mind that the change of days of the week occurs through the date always entered in cell (M1)?
I myself tried this Formula written in the line (A2:G2)
But it doesn't work Why and how can I solve this problem.
Thank you
Trusted Members
October 17, 2018
Offline
Set the first legget in gggg as Ggggg ?
June 26, 2016
Offline
Hello Hans Hallebeek
Thanks for your suggestion, but maybe I explained myself badly; Therefore I repeat myself.
If you looked at my project you would notice that in the A1:G1 line I started writing this formula: =DATA(M2;M4;1)
to be able to have, after formatting, the first day of the week of the start of the desired month written in the column (M1:M4).
Except that the Format is reported to me with the writing (Friday); I'd like to see it get written (Friday - Saturday - Sunday) etc....!
Now to get this: in Row (A2:G2) I thought of writing the formula:
=DATE(M2;M4;1) & " " & TEXT(M6;"dddd") except that this solution gives me both the Serial number (45352) and the Day of the Week as I want (Friday).
Now my question is this: There would be no way to hide the serial number leaving only the Stritta Friday.
But it can in turn work well for all the other days of the week: (Friday - Saturday - Sunday) etc...!
Which however is not happening at the moment!
Moderators
January 31, 2022
Offline
When I open your file and look at the formula in A2 I see this:
=DATE(M2,M4,1) & " " & TEXT(M6,"gggg") & MID(A2,1,5)
Let's break this in pieces:
DATE(M2,M4,1) returns the first day of the month (March 1, 2024, a Friday). However, you mention that it returns the first day of the week. So what is correct?
TEXT(M6,"gggg") would make sense if M6 would be a date. But M6 contains a text "Venerdi". Thus, the TEXT function and the Italian "gggg" format to display the name of the weekday does nothing. It just returns "Venerdi". You could also just refer to M6.
MID(A2,1,5) causes a circular reference and after ignoring the warning, it returns 0. In any case, if this had been referring to another date cell it would return the first five digits of the date value.
Obviously, you want to achieve something different, but it's not clear to me what that is. Perhaps in A2 something simple like this?
=TEXT(A1,"dddd") and drag it across.
Trusted Members
October 17, 2018
Offline
Maurizio,
You say you want to see the first day of the WEEK of the desired month but this is the first dat of the MONTH.
March 2024 starts on a Friday. So this part is correct.
The custom date format "Ddddd" will give you the weekdays name in uppercase.
I do not understanbd what you ware trying to achieve with all the complicate formulas
June 26, 2016
Offline
Hello Riny van Eekelen and Hans Hallebeek
I understand that my English leaves a little to be desired, due to the Google translator
But not being at your level as a programmer, I always try to look for the easiest and most plausible explanation to solve my problems that I ask myself a priori.
Therefore, thanks also to your suggestions, I have presumed clarifications on the matter. I finally managed to achieve what I wanted to achieve from the beginning by simply doing it this way:
I took as good what I had written in the row (M6:S6) therefore I started by writing the formula "=Date(M2;M4;1) in the cell (M6) in this way I transformed everything into a start date Month
then I transform it into the day of the week which is a (Friday), after which I transform all the other adjacent cells into the days of the rest of the week like this (M6+1) To get Saturday (N6+1) To get Sunday etc....!
Then in Row (A2:G2) I write this other formula for each cell which is this:
=INITIAL SHIFT(TEXT(M6,"Dggdd")) By doing this in cell (A2) I obtain the day of the week written like this "Friday"
And so for all the other adjacent cells.
And at the end of it all I simply get the first letter of the days of the Week in capital letters all here!
("Monday - Tuesday - Wednesday") etc....!
Thanks again for your suggestions
However, you were helpful to me and I was once again able to take advantage of your knowledge; Which for me are Cast Gold. Thank you
Greetings to everyone from Maurizio
1 Guest(s)
