Extracting Month from Date and doing a calculation

[gb]

I have a large spreadsheet, which is all the sales in my company on a
daily basis.

The data contained in cell O5 is the actual date based as dd/mm/yyyy
with cell Z5 containing the monetary value of that sale.

I need to extract on a monthly basis all the sales per month.

I was trying to use

=IF(O5="april",Z5)

This does not work. So how do I extract the month from a date. Or any
other ideas of how I could do it ??.

TIA

 

[Dave Peterson]

=if(month(o5)=4,z5,"whatgoeshere")

I have a large spreadsheet, which is all the sales in my company on a
daily basis.

The data contained in cell O5 is the actual date based as dd/mm/yyyy
with cell Z5 containing the monetary value of that sale.

I need to extract on a monthly basis all the sales per month.

I was trying to use

=IF(O5="april",Z5)

This does not work. So how do I extract the month from a date. Or any
other ideas of how I could do it ??.

TIA

 

[Guest]

You might consider using the AutoFilter to filter and display all of the
sales for the month of April, then using the =SUBTOTAL(9,Z:Z) formula to get
the total sales for that month. This way you could also actually "see" all
the April sales and could maybe decern other interesting info besides just
the total sales.

Vaya con Dios,
Chuck, CABGx3

 

[Sandy Mann]

=if(month(o5)=4,z5,"whatgoeshere")

Being picky here, but won't that, (when used for Month 1) return Z5 even for
blank cells?

Better to use something like:

=IF(AND(O5<>"",MONTH(O5)=1),Z5,"whatgoeshere")

(Ok I'll get back to looking at the code you so helpfully posted for me <g>)

--
Regards

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[gb]

GR8 that works fine.

The only other problem I have got now , is that the date cell,
sometimes contains a piece of text 'unsold'

This then throws up a #VALUE! error

If the formulae comes across this piece of text, then the cell should
then equate to 0

So how can I incorporate this problem into the IF statement.

 

[Peo Sjoblom]

=IF(AND(ISNUMBER(O5),MONTH(O5)=4),Z5,0)

 

[gb]

Cannot get that to work.

Maybe its because cell O5 is a date and not a number ???

I'm not sure.

 

[Peo Sjoblom]

Either your date is not a date that excel recognizes (meaning it is text) or
if it's a date then it's not April

Excel dates are numbers where one day is 1 and counting from Jan 0 1900
meaning that today is 38720

put 38720 in a cell, then format the cell as a date

 

[Dave Peterson]

Dates are just numbers to excel.

Maybe this:

=IF(ISNUMBER(O5)=FALSE,0,IF(MONTH(O5)=4,Z5,0))
or
=IF(NOT(ISNUMBER(O5)),0,IF(MONTH(O5)=4,Z5,0))