Pulling month/year from date/time stamp

[DjembeDog]

I need to count cells of various months, but the cells autopopulate from the database with date and time. (i.e., 6/26/2008 1:16:00 PM)

Can I "convert" or "extract" the month and year from each cell via a formula so that only the month and year remain?

Note: Changing the format of the cell only changes the view rather than the data content.

Many thanks.

 

[barry houdini]

You could extract the year and month as text with

=TEXT(A1,"mmm-yy")

or convert to the first of the month with

=INT(A1)-DAY(A1)+1

but if you want to count dates in July 2008, for instance you could do that directly with a formula like

=SUMPRODUCT(--(TEXT(A1:A100,"mmm-yy")="jul-08"))

 

[QGroup]

Use =MONTH() and =YEAR() functions to extract the month and year as numbers

 

[MRA]

I have the same question, and I tried using the =month() and =year() fxns but they don't recognize the format the data is in.
my A2:A16241 contains dates of format:

Jun-17-2004 12:50:00

and I want to extract month, year, and hour in other columns.
I tried using a custom format but that does not seem to change anything.
Can anyone help?

 

[barry houdini]

Your data is text formatted. You should be able to extract month, year and hour with these formulas

=MONTH(1&LEFT(A1,3))
=MID(A1,FIND(" ",A1)-4,4)+0
=MID(A1,FIND(" ",A1)+1,2)+0

or assuming that the day is always shown as 2 digits [i.e. 1st Jul would be Jul-01-2004 12:50:00] you can convert the whole thing to a recognisable date/time with this formula

=(MID(A1,5,3)&REPLACE(A1,4,3,""))+0

format result cell as required and use YEAR, MONTH and HOUR function to extract the data you need

 

[MRA]

I wasn't able to get the formulae involving mid to work, but the month formula did.

I asked someone else in the office tho, and they suggested using find and replace, in this case finding JUN and replacing with 6, etc
this worked like a charm!

Thanks for your help guys

 

[lenze]

Can you not use a Pivot Table with your field grouped by month?? Would probably take about 10 secs to set up and you can all months at once (No formulas neeeded)

lenze

 

[DjembeDog]

Many thanks, all. You gave me what I needed.