Calculating number of days

Hello,I'm trying to make a VBA script that will calculate dynamically the number of days in a specific year for chosen month which will later be used for simple math.

My table looks like this:

A2 = 2019 (here I manually type in your number)
A5 = is a data validation list with months (this is generated with VBA)
A8 = is returned number of days in the selected month for 2019 here is my VBA code:

Please Login or Register  to view this content.

My knowledge of VBA is very basic and all though this is working is it possible to make it work, update results in A8 when I choose different item from the list?And if something is wrong with the code please do tell me... all the best G.

Originally Posted by GOR4N

... A8 = is returned number of days in the selected month ...

Maybe formula ?
In 'A8':

=IF(OR(A5={"January","March","May","July","August","October","December"}),31,IF(OR(A5={"April","June","September","November"}),30,IF(A5="February",IF(MOD(A2,4)=0,29,28),"")))

Originally Posted by Rick Rothstein

... if ... work correctly for the OP's locale ...

Of course, after all, "January" is not "January" (etc.) in every language, it could be e.g. "Sausis", "Janvier", "Stud'yen'", "Siječanj", "Enero", "Gener", etc. ...

Originally Posted by porucha vevrku

Of course, after all, "January" is not "January" (etc.) in every language, it could be e.g. "Sausis", "Janvier", "Stud'yen'", "Siječanj", "Enero", "Gener", etc. ...

Which is why I said "providing correctly spelled month names for that locale are used in cell A5" in my first paragraph. I believe, but am not 100% sure, that Excel will convert text in the form... DayNumber, Space, MonthName, Space, Year... to a real date where the MonthName is correctly spelled for that locale.

Hi all ... tnx for quick answers

mr.bakerman2 ... for some reason this one didn't worked for me

Originally Posted by bakerman2

Please Login or Register  to view this content.

The second one from vevrku gave me some function error

Originally Posted by porucha vevrku

Maybe formula ?
In 'A8':

=IF(OR(A5={"January","March","May","July","August","October","December"}),31,IF(OR(A5={"April","June","September","November"}),30,IF(A5="February",IF(MOD(A2,4)=0,29,28),"")))

What I had to do is replace , with ; and do some small changes to function
In the end working function looks like this

Formula:

Please Login or Register  to view this content.

For me it's working now as I wanted but if some of you guys are interested why errors are happening I am more than willing to do some debuging with you guys.

Tnx again ... G.

or , or ; or \ or others - it depends on the system settings: date, separators, etc.

Variants:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The end part of your final formula is trying to calculate whether the year is a leap year or not, but you should be aware that a century year (1900, 2000, 2100 etc.) is NOT a leap year, even though it is divisible by 4, so you need to make a slight adjustment if you want to cope with those years correctly.

Hope this helps.

Pete

Originally Posted by GOR4N

For me it's working now as I wanted but if some of you guys are interested why errors are happening I am more than willing to do some debuging with you guys.

Did you ever try either the code or the formula that I posted in Message #4? I am curious if either of them work (as posted) in your locale.

Originally Posted by Pete_UK

... you should be aware that a century year (1900, 2000, 2100 etc.) is NOT a leap year ...

Wow, Do you plan to use excel in year 2100, etc. ?

... then there will be no Excel and nothing from the Office also ... not to mention us ...

Everything in Excel, including functions/formulas, is subject to restrictions

We don't know what the OP is using this for - s/he talks about using the value "which will later be used for simple math".

They might want to do this for the year 2000 - who knows ??

Pete

... something from before 1900 ... ... that would be fun ... e.g. February 25, 9999 BC ...

Just noting that there is a very simple test whether a year is a leap year or not...

In VBA, test this... Month(DateSerial(Yr, 2, 29)) = 2

In a formula, test this... MONTH(DATE(A2,2,29))=2

Where Yr is a variable containing a year value and A2 is assumed to be a cell containing a year value.

It's an easy modification to the earlier formula - instead of:

… IF(MOD(A2;4)=0;29;28) ...

near the end, you would have:

… IF(MOD(A2;100)=0;28;IF(MOD(A2;4)=0;29;28)) …

Hope this helps.

Pete

Originally Posted by Pete_UK

It's an easy modification to the earlier formula - instead of:

… IF(MOD(A2;4)=0;29;28) ...

near the end, you would have:

… IF(MOD(A2;100)=0;28;IF(MOD(A2;4)=0;29;28)) …

Or using what I posted in Message #14...

28+(MONTH(DATE(A2,2,29))=2)

Originally Posted by Pete_UK

… IF(MOD(A2;100)=0;28;IF(MOD(A2;4)=0;29;28)) …

Leap year = True <=> ( (year mod 4 = 0) and (year mod 100 <> 0) ) or (year mod 400 = 0)

("https://en.wikipedia.org/wiki/Leap_year")

But the formula will be more complicated, why complicate our life ?

Originally Posted by Rick Rothstein

... the following code works here in the US ...

Originally Posted by Rick Rothstein

... but am not 100% sure, that Excel will convert text ...

Originally Posted by GOR4N

... I am more than willing to do some debuging with you guys ...

We can always rack our brains

https://docs.microsoft.com/en-us/off....msolanguageid
https://docs.microsoft.com/en-us/ope...d-c3b0ee3dbc4a

"January" in English is "January" => TEXT(DATE(2019, 1, 1),"[$-" & DEC2HEX(1033) & "] mmmm")
"January" in Danish is "Januar" => TEXT(DATE(2019, 1, 1),"[$-" & DEC2HEX(1030) & "] mmmm")
"January" in Lithuanian is "Sausis" => TEXT(DATE(2019, 1, 1),"[$-" & DEC2HEX(1063) & "] mmmm")
etc.
Of course, appropriate system separators should be included (, ; ...)

Originally Posted by porucha vevrku

But the formula will be more complicated, why complicate our life ?

Did you see Messages #14 and #16 yet? Checking if a year is a Leap Year or not is not complicated at all in Excel.

We've heard very little from the OP (not since Post #7), so I think I shall unsubscribe from this thread.

Pete

i did try but it didn't work ... i don't get error but instead it keept me asking to "choose macro" dialog