month value only

T

taniedzw

I have a spreadsheet in which I need to calculate values based on th
month. my column A is a list of date values in the format "Jan-04"
When I highlight the cell it says 1/4/2004.

This is a problem for me becaues it only references data from the 4t
day of the month whereas I need the whole month. How do I make it onl
reference the month and year, for example

Jan-04 means any date in January 2004 and the corresponding formul
will recognize all dates in that month, not just the 4th day of th
month.

Thank you for the help!

Ti
 
P

Peo Sjoblom

You can't if you want to use dates, you could use a text format and precede
the entry with an apostrophe '
that way you can use for example

=SUMIF(A:A,"Jan-04",B:B)

however you can use something else even with the dates you are using

=SUMPRODUCT(--(YEAR(A1:A1000)=2004),--(MONTH(A1:A1000)=1),B1:B1000)

will work for January 2004. Also note that if you don't want the 4th of
every month you have to enter it as
Jan-2004, that will return the first date of every month so instead of
01/04/04 you will get 01/01/04
the reason is that Excel interpret Jan-04 as the 4th of January current
year, if you put in the year 2004 it will use
the 1st of the month

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
B

Bob Phillips

One way is to check that the date is within the first and last day of the
month, such as

Date(Year(TODAY()),1,1) - first day of month

and

DATE(YEAR(TODAY()),2,0) - last day of month

--

HTH

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

Frank Kabel

Hi
if you want for example sum column B only for January 2004 use
=SUMPRODUCT(--(MONTH(A1:A100)=1),--(YEAR(A1:A100)=2004),B1:B100)
 
T

taniedzw

Hi again,

Here is what I have so far

(Sheet 4)
row 1 (column B thru column AC) is a list of Vendors
Column A (row 2-13) is a list of months from Jan-Dec

(Data Log)
In another sheet I have
-a list of dates (day,mo,year)
-a list of vendors
-a list of hours lost

I need to have (in Sheet 4) the data for hours lost for example

vendor is gilmore
month is april

hours lost in april for vendor gilmore

the formula i have only references one day of the month, not ever
value in the month, here is the formula in Sheet 4.

=SUMPRODUCT(--('Data Log'!$O2:$O999=($A2)),--('Dat
Log'!$L2:$L999=C$1),'Data Log'!$C2:$C999)

Where column A is in the format mmm yy

I tried this formula but it returns all 0's

=SUMPRODUCT(--(MONTH('Data Log'!$B$2:$B$999)=1),--(YEAR('Dat
Log'!$B$2:$B$999)=2004),--('Data Log'!$L$2:$L$999=B$1),'Dat
Log'!$C$2:$C$999)

I don't know if I'm explaining this well enough or not, so if you nee
more info just ask.

Thanks!

Ti
 
F

Frank Kabel

Hi
how do enter your date values (give an example9. are you entering them
as real Excel dates?
 
T

taniedzw

I have a master log sheet called Data Log

I wrote a VBA code to have a user enter the required info in a form an
it inserts it in the next available row in the database. the use
enters a date value in a text box in the form by just typing it in.
The column containing the dates in the master log is formatted to
date value (ie, 1/1/04) format.

I would like to reference those dates from that column in my formula
 
F

Frank Kabel

Hi
if you like, email me your file
email: frank[dot]kabel[at]freenet[dot]de

Please also describe your expected result + that is not working with
your current formula in this mail
 
Top