Share via

Month formula not working correctly in Excel

Anonymous
2016-01-30T22:27:06+00:00

I have had this problem before but it is inconsistent below are the formulas that I used to determine the date, day of week and the month. Month is returning January when it is clearly February.

=DATE(2016,2,1) =IF(G4="","",WEEKDAY(G4,1)) =MONTH(G4)
=DATE(2016,2,2) =IF(G5="","",WEEKDAY(G5,1)) =MONTH(G5)
=DATE(2016,2,3) =IF(G6="","",WEEKDAY(G6,1)) =MONTH(G6)
=DATE(2016,2,4) =IF(G7="","",WEEKDAY(G7,1)) =MONTH(G7)
=DATE(2016,2,5) =IF(G8="","",WEEKDAY(G8,1)) =MONTH(G8)
=DATE(2016,2,6) =IF(G9="","",WEEKDAY(G9,1)) =MONTH(G9)
=DATE(2016,2,7) =IF(G10="","",WEEKDAY(G10,1)) =MONTH(G10)
2/1/2016 Monday January
2/2/2016 Tuesday January
2/3/2016 Wednesday January
2/4/2016 Thursday January
2/5/2016 Friday January
2/6/2016 Saturday January
2/7/2016 Sunday January
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-01-30T23:19:17+00:00

    Re:  numbers and dates

    If the cell is formatted as "mmmm" then 2 is the second day of the year, which falls in January.

    With 32 you get February.

    '---

    Jim Cone

    Portland, Oregon USA

    https://goo.gl/IUQUN2 (Dropbox)

    (free & commercial excel add-ins & workbooks)

    20+ people found this answer helpful.
    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2016-01-30T23:10:15+00:00

    Hi,

    The result of a MONTH() function is always a number.  Perhaps you are getting January because the cell has been Custom formatted or the cell is blank.

    Try this formula to get the month

    =IF(G4="","",TEXT(G4,"mmmm"))

    Hope this helps.

    20+ people found this answer helpful.
    0 comments