dates typed vs =month()

[dmheller]

So this may be dumb but I type in January and then auto fill down and get to December. I think use that as a month ref in an equation
=month(a1) where January is typed and I get an error. What do I need to do so that I can type a month and have excel know that when I use the month function?

 

[Joe4]

The MONTH function pulls the month off a valid date entry.
But you do not have a valid date entry in A1, you just have a month name.

If you need to get the month, just use:

=A1

instead of

=MONTH(A1)

 

[dmheller]

So if I use a sumproducts and ref A1 instead of month(a1) will it sum January or will it not see it as a date because all other data is in date form

 

[Joe4]

If you are looking for monthly totals using SUMPRODUCT, see if this helps: https://excelzoom.com/sum-values-month/

 

[dmheller]

Ok, that's what I thought excel does not see a typed month as a month but just text so I have to trick it

 

[Joe4]

Ok, that's what I thought excel does not see a typed month as a month but just text so I have to trick it

That is correct.

It is also important to see how Excel store dates. It actually stores them as numbers, the number of days since 1/0/1900 (time component is a fraction of a day, so shows up as decimals).
So a valid date entry is really just a number that has a date format on it. To see this, enter any date, and change the cell format to "General", and you will see the date as Excel sees it.

So, if you ever have a question regarding a date entry if it is really a date or text, you can either:
- change the cell format to "General" and see if it changes it to a number
or
- use the ISNUMBER function on it and see if it returns TRUE