Extracting Month from Date and doing a calculation

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

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



gb wrote:
>
> 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

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





"gb" wrote:

> 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" <[email protected]> wrote in message
news:[email protected]...
> =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
[email protected]
[email protected] with @tiscali.co.uk

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> =if(month(o5)=4,z5,"whatgoeshere")
>
>
>
> gb wrote:
>>
>> 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

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.


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

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

--

Regards,

Peo Sjoblom

"gb" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> >=if(month(o5)=4,z5,"whatgoeshere")
>
>

Cannot get that to work.

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

I'm not sure.

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

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


--

Regards,

Peo Sjoblom


"gb" <[email protected]> wrote in message
news:[email protected]...
> Cannot get that to work.
>
> Maybe its because cell O5 is a date and not a number ???
>
> I'm not sure.
>
> >=IF(AND(ISNUMBER(O5),MONTH(O5)=4),Z5,0)
>

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))


gb wrote:
>
> Cannot get that to work.
>
> Maybe its because cell O5 is a date and not a number ???
>
> I'm not sure.
>
> >=IF(AND(ISNUMBER(O5),MONTH(O5)=4),Z5,0)

--

Dave Peterson