Why isn't the =month() formula working?
Solved
mickysor
-
mickysor -
mickysor -
Hello,
my monthly formula isn't working! It's either showing the year, or the day, or nothing at all!
I'm sending you an attachment
http://www.cijoint.fr/cjlink.php?file=cj201102/cijJ7HE6bi.xls
Thank you for your help.
my monthly formula isn't working! It's either showing the year, or the day, or nothing at all!
I'm sending you an attachment
http://www.cijoint.fr/cjlink.php?file=cj201102/cijJ7HE6bi.xls
Thank you for your help.
8 answers
Hello everyone,
Your mistake comes from the fact that you want to work on the year 10; you can try transforming until tomorrow, it won’t work.
Solution... change all the cells where the year is 10 to 2010
See you
EDIT:
You paste this code into the Feuil1 module
You place the cursor in the macro and press F5.
There is still one error that you need to fix manually.
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to PMs for technical questions.
It should take place on the forum so that everyone can participate or benefit.
Your mistake comes from the fact that you want to work on the year 10; you can try transforming until tomorrow, it won’t work.
Solution... change all the cells where the year is 10 to 2010
See you
EDIT:
You paste this code into the Feuil1 module
Sub Convertir() Dim Lig As Integer, TB For Lig = 1 To Range("A65536").End(xlUp).Row TB = Split(Cells(Lig, 1), "/") On Error Resume Next If TB(2) < 2000 Then Cells(Lig, 1) = TB(0) & "/" & TB(1) & "/2010" Next Lig End Sub You place the cursor in the macro and press F5.
There is still one error that you need to fix manually.
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to PMs for technical questions.
It should take place on the forum so that everyone can participate or benefit.
Your macro seems to work for transforming .10 into 2010, but I'm unable to get the formula to work on certain months. Some of these dates are aligned to the left of the cell on my end. There might be an option to activate in order to make the dates work. I should mention that I've tried all the methods above.
I tested your spreadsheet, and aside from two or three errors, everything was normal.
I formatted the column to >> Custom >> dd/mm/yyyy
Whether it was displayed on the right or the left didn't matter.
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
It should take place on the forum so that everyone can participate or benefit.
I formatted the column to >> Custom >> dd/mm/yyyy
Whether it was displayed on the right or the left didn't matter.
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
It should take place on the forum so that everyone can participate or benefit.
Your binder is back
To apply the format, you had to select the ENTIRE column; the format was not applied to the whole column.
There are still, as I said, some errors, but that's because the cells do not correspond to a date.
See you later
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
It has to be discussed on the forum so that everyone can participate or benefit.
To apply the format, you had to select the ENTIRE column; the format was not applied to the whole column.
There are still, as I said, some errors, but that's because the cells do not correspond to a date.
See you later
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
It has to be discussed on the forum so that everyone can participate or benefit.
And with my binder, what does it give?
when I type the date 07/12/10 it gives me 12/07/10 when I validate it
OBVIOUSLY... wrong, type 07/12/2010
and if your data comes from outside, run the macro again after importing.
EDIT:
Seeing your image... you are not formatting as indicated above
see this example
I tested by entering 07/12/10 and the date correctly shows up as 07/12/2010
But what's strange is that when I copy and paste your example, the day and month are inverted. Where does your data come from?
Experience teaches more surely than advice. (André Gide)
If you hit a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
It should take place on the forum so that everyone can participate or benefit.
when I type the date 07/12/10 it gives me 12/07/10 when I validate it
OBVIOUSLY... wrong, type 07/12/2010
and if your data comes from outside, run the macro again after importing.
EDIT:
Seeing your image... you are not formatting as indicated above
see this example
I tested by entering 07/12/10 and the date correctly shows up as 07/12/2010
But what's strange is that when I copy and paste your example, the day and month are inverted. Where does your data come from?
Experience teaches more surely than advice. (André Gide)
If you hit a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
It should take place on the forum so that everyone can participate or benefit.
Hello everyone
I might be saying something silly, but it seems that all the dates where the day exceeds 12 in the table automatically turn into text:
to check:
start menu
control panel
regional and linguistic options
check in the advanced "custom" tab "date" if the date format is indeed:
dd/MM/yyyy and not reversed!
just in case
best regards
--
Let’s ask ourselves if we are not the only ones to understand what we are explaining?
I might be saying something silly, but it seems that all the dates where the day exceeds 12 in the table automatically turn into text:
to check:
start menu
control panel
regional and linguistic options
check in the advanced "custom" tab "date" if the date format is indeed:
dd/MM/yyyy and not reversed!
just in case
best regards
--
Let’s ask ourselves if we are not the only ones to understand what we are explaining?
Hello Vaucluse,
I checked the format but it remains in dd/mm/yyyy and as mentioned above, it's when I copy SA data that it gets reversed.
I think the problem comes from the data source; we will need to format the import, that's why I was asking for the source.
See you later
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
It should take place on the forum so that everyone can participate or benefit.
I checked the format but it remains in dd/mm/yyyy and as mentioned above, it's when I copy SA data that it gets reversed.
I think the problem comes from the data source; we will need to format the import, that's why I was asking for the source.
See you later
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
It should take place on the forum so that everyone can participate or benefit.
Good evening lermite222
I don't know what to say... when I take the file under 2007, it automatically realigns all the dates and everything works!
But that's probably what you're seeing.. I also don't understand why Eriiic's suggestion, repeated below, doesn't work here
to transform them into numerical:
- copy an empty cell
- select column A
- special paste 'addition'
best regards
I don't know what to say... when I take the file under 2007, it automatically realigns all the dates and everything works!
But that's probably what you're seeing.. I also don't understand why Eriiic's suggestion, repeated below, doesn't work here
to transform them into numerical:
- copy an empty cell
- select column A
- special paste 'addition'
best regards
re,
Of course it works, here is the result (I reverted to a date format after the numeric conversion): http://www.cijoint.fr/cjlink.php?file=cj201102/cijGKDlcXh.xls
Moreover, I don't think turning 10 into 2010 adds much because that's what Excel does on its own with a date.
However, the day/month inversion occurs if, during the import, the date format is not specified on the column (at step 4), or with certain VBA functions that do not take regional format into account (in that case, combine with format or use datevalue which works correctly).
eric
Of course it works, here is the result (I reverted to a date format after the numeric conversion): http://www.cijoint.fr/cjlink.php?file=cj201102/cijGKDlcXh.xls
Moreover, I don't think turning 10 into 2010 adds much because that's what Excel does on its own with a date.
However, the day/month inversion occurs if, during the import, the date format is not specified on the column (at step 4), or with certain VBA functions that do not take regional format into account (in that case, combine with format or use datevalue which works correctly).
eric
I finally managed to solve this riddle thanks to Vaucluse mentioned earlier! I just had to go to the control panel and change the regional languages to French!
Thank you all for your collaboration and dedication; this topic has finally been resolved!
The English system is totally different. It's good to know for others who have this kind of problem!
Thank you all for your collaboration and dedication; this topic has finally been resolved!
The English system is totally different. It's good to know for others who have this kind of problem!
Curiously, I opened the template file and, on the left or right, without touching anything, the formula works across the entire height?
Cheers
It's that you have Excel >= 2007.
On the MONTH() function, it evaluates the parameter and converts it to a number if it's a valid date.
Just like in 2003, it performs a conversion if you do + - etc.
And to know how it's going this Sunday, I'll wait until tomorrow to answer you (you should know, you're not at the market) ;-)
Otherwise, today is going very well thank you, and you? :-)
Eric
Yes!
Eric