Formating MONTH() integer

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

In the MONTH() function, I can get the integer returned OK,
but when I format that cell with "mmm" or "MMMM", it always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Dan,

To get the true month, you need to use

=TEXT(date,"mmm")

If you use

=TEXT(MONTH(date),"mmm")

it treats MONTH(date) as a date, which will be sometim in Jan 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Yep, I got that far, but I need the MONTH() integer to
calculate other cells. I want the TEXT to sho in the cell,
but I need the integer result for other calcs.
 
Then use

=CHOOSE(MONTH(date),"Jan", "Feb", "Mar", etc. )

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You can't, at least not directly.

Can't you use the format mmmm and then wrap it in month
in the other calculations,
 
That still doesn't return the month integer (1 to 12).
That's the number I need for other calcs. I still want
that cell to sho the text month (Jan etc.) but I need that
integer. With WEEKDAY func. I keep the integer of the cell
but format it with "dddd" from the format menu. That does
not work with the "mmm" format using the MONTH integer.

Thanks for all your help, but I'm thinking that it just
can't be done. All the best.
 
You can't both have the cake and eat it! Just format the cell as mmm
and then in whatever formula you are using wrap it in the month function.

Assume the cell is in A2, just use custom format mmm,
now let's say you want to use the integer part to count January dates

=SUMPRODUCT(--(MONTH(A2:A100)=1),--(ISNUMBER(A2:A100)))
 
Exactly!

Bob

Peo Sjoblom said:
You can't both have the cake and eat it! Just format the cell as mmm
and then in whatever formula you are using wrap it in the month function.

Assume the cell is in A2, just use custom format mmm,
now let's say you want to use the integer part to count January dates

=SUMPRODUCT(--(MONTH(A2:A100)=1),--(ISNUMBER(A2:A100)))

--

Regards,

Peo Sjoblom
Click to expand...
 
Thanks, but... I'm a little lost here. If you mean using
the mmm from the format menu, then it doesn't work. Always
returns January no matter what. And the example you posted
is above my pay grade. I don't quite follow it. Sorry.
But thanks anyway.
 
Hi Dan
Peo's and Bob's solution require the following:
1. In your cell you have to store the complete date (e.g. 01/01/2004)
2. You can format this date with the custom format 'MMM' or 'MMMM' to
get the name of the month displayed in your cell
3. if you need the month's number you can easily use
=MONTH(A1)
where A1 is the cell that stores your date

Peo just gave you a more complex formula example to use this MONTH
function
 
Hi Dan!

A bit of explanation!

=MONTH(A1) Will return an integer between 1 and 12

If you format mmm or mmmm you are formatting as a date and the date
serial numbers 1-12 represent 1-Jan-1900 to 12-Jan-1900. So that is
why you always get January.

With =WEEKDAY(A1) You get integers between 1 and 7. If you format ddd
or dddd you get the correct day of the week. But that is only because
by accident Excel has date serial number 1 as Sunday 1-Jan-1900 when
that day was in fact a Monday.

The only possibility here of having your cake and eat it is

=MONTH(A1)*29
Formatted mmm or mmmm.

You're still getting dates in 1900 but the dates 1*29; 2*29 etc are
all in consecutive months.

But thereafter you'll have to remember that the underlying number has
to be divided by 29.

Can't really see much use for this approach but it is an alternative
to CHOOSE

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks Bob.
That is a good one... Just in time too.
I needed some automatically changing titles like "February EOM"
so I just modified it to this

=TEXT($F$1,"mmmm")&" EOM"

Which helps because I need the text titles automatic.

Any ideas to display the previous month?

I needed to pull out the working days only M-F.

<haven't figured out the 'holidays' exclusions yet, except using a truth
table.>

Here is how I solved it for now with this:
Where WORKDAY is "defined" as
=IF(WEEKDAY(Yoda!$A2+1,2)=6,IF(WEEKDAY(Yoda!$A2+2,2)=7,Yoda!$A2+3,Yoda!$A2+2
),Yoda!$A2+1)

Where Yoda is the name of the sheet tab (just to pick something)
Next:
The first day is plugged in A5 then then the following rows (a6:a30) is
=IF(MONTH($A$5)=MONTH(WORKDAY),WORKDAY," ")

It's not perfect but it works for now.
I'll have to figure out a 1st day of month, last day of month detector and
print blanks for
pulled months of variable days.
Thanks for posting.

Chris
(e-mail address removed)
 
Graystar said:
Thanks Bob.
That is a good one... Just in time too.
Click to expand...

Great, glad some good came out of this thread.
I needed some automatically changing titles like "February EOM"
so I just modified it to this

=TEXT($F$1,"mmmm")&" EOM"
Click to expand...

Excellent, that is how I would do it.
Any ideas to display the previous month?
Click to expand...

Not straight-forward, but nt hard

=TEXT(DATE(YEAR($F$1),MONTH($F$1)-1,DAY($F$1)),"mmm") & " EOM"

But you have to be careful. For instance, if F1 has 31st March, take 1 month
off as I have shown, and you get 31st Feb, which Excel will smartly see as
2nd March (in a leap year), so will not be what you want. Will you have this
problem?

I needed to pull out the working days only M-F.

<haven't figured out the 'holidays' exclusions yet, except using a truth
table.>

Here is how I solved it for now with this:
Where WORKDAY is "defined" as
=IF(WEEKDAY(Yoda!$A2+1,2)=6,IF(WEEKDAY(Yoda!$A2+2,2)=7,Yoda!$A2+3,Yoda!$A2+2
),Yoda!$A2+1)

Where Yoda is the name of the sheet tab (just to pick something)
Next:
The first day is plugged in A5 then then the following rows (a6:a30) is
=IF(MONTH($A$5)=MONTH(WORKDAY),WORKDAY," ")

It's not perfect but it works for now.
I'll have to figure out a 1st day of month, last day of month detector and
print blanks for
pulled months of variable days.
Click to expand...

Not sure what you are driving at here, so no suggestions yet. But have you
checked out the WORKDAY and NETWORKDAYS functions, part of the Analysis
Toolpak, which manage holidays. Do you want to explain what you are doing
with the dates re M-F?
 
Bob Phillips said:
in message

Great, glad some good came out of this thread.


Excellent, that is how I would do it.


Not straight-forward, but nt hard

=TEXT(DATE(YEAR($F$1),MONTH($F$1)-1,DAY($F$1)),"mmm") & " EOM"

But you have to be careful. For instance, if F1 has 31st March, take 1 month
off as I have shown, and you get 31st Feb, which Excel will smartly see as
2nd March (in a leap year), so will not be what you want. Will you have this
problem?
Click to expand...

I'm not sure yet. It might.
I'll have to play with it to see if an error occurs.
I don't know if it will be an issue in the future
since new solutions tend to make some answers moot even as they are solved.

As an alternative
I could have the equation do a live compare:
extracting the month from the date from the 1st report for that month
compared to the month of the current date report
and
when the day value exceeds a number like 27 or 28
then I could default the month values for the titles
as a derivation from the 1st report's date (month value).

Kinda far to go, but I can't see any hitches to it,
unless part of the months reports are moved or deleted.
That is what I come up with as an immediate stream-of-conscousness solution.
Perhaps a little reflection and I'll come up with a better one.

Each report is done by the day and stored in that months folder.
New month, new folder.
Ideally this all should be done in Access, imo,
and spit out some Excel sheets autoformatted for emailing to the appropriate BigWig.
That way Year End Reports would be a snap.
Not sure what you are driving at here, so no suggestions yet. But have you
checked out the WORKDAY and NETWORKDAYS functions, part of the Analysis
Toolpak, which manage holidays. Do you want to explain what you are doing
with the dates re M-F?
Click to expand...

Not seen the Toolpak yet, or where to acquire it.
As to the reason for the WORKDAY function I made it detects M-F

Explanation:

Assume A5 is the 1st workday of the month that you plug in manually.

Tou define the following eq. below as "WD" in the Name->Define section.
=IF(WEEKDAY($A5+1,2)=6,IF(WEEKDAY($A5+2,2)=7,$A5+3,$A3+2),$A5+1)
This equation detects for Saturday or Sunday and adds the appropriate number to "skip" the required days to get to the next workday... but it does not handle blocking the detection for EOM (end of month).

NOTE: for this discussion I changed the name of the function I created
"WORKDAY" to "WD" because as I now understand it
there is such a function of the same name in the Excel Toolpak.

That's where you past in this equation for the range A6:A31.
=IF(MONTH($A$5)=MONTH(WD),WD," ")
The conditional checks the 1st date for the month and compares it to the autoflowed equation values
determined by "WD"
It rejects anything out of the month comparison by giving a blank.

<There is a #VALUE! error after the second out of month values returned I have yet to fix>

Example: where bold is a plugged #, Day#=Count($A$5:A5) for the 1st cell
and =IF(MONTH($A$5)=MONTH(WORKDAY),COUNT($A$5:A6)," ") for the cells below,
and
Day of Wk=DAY(value in column A)=DAY(A5) for 1st cell example.

date
Day #
Day of wk

02/02/04
1
2

02/03/04
2
3

02/04/04
3
4

02/05/04
4
5

02/06/04
5
6

02/09/04
6
9

02/10/04
7
10

02/11/04
8
11

02/12/04
9
12

02/13/04
10
13

02/16/04
11
16

02/17/04
12
17

02/18/04
13
18

02/19/04
14
19

02/20/04
15
20

02/23/04
16
23

02/24/04
17
24

02/25/04
18
25

02/26/04
19
26

02/27/04
20
27





#VALUE!



#VALUE!






The rationales and goals.
<I want it to eventually exclude designated work holidays>
since the time the business is open is only on weekdays
excluding major designated holidays.

Week Workdays are the only days that have daily reports.

A report for the progress on a specific day is always done the next day,
as most of the IT staff goes home by 5pm.
So to report the full day,
The report is completed on the next day.
Data sheets are printed <yep stone age methods>
on a fast dot matrix/fan fold paper printout...
because there is no *soft* copy available.

Annoying, but that's as far as their integration has progressed,
and why it's not just dumped into something like Access
and then auto-dressed-up with Excel for the finished formatted reports.

Feel free to comment or ask a question.
My email gets about 80% spam so it's prolly best to discuss it here.
<grin>
Chris Hufford
Graystar
(e-mail address removed)
 
Back
Top