# Extracting Month from Date and doing a calculation

1. ## 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

2. ## Re: Extracting Month from Date and doing a calculation

=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

3. ## RE: Extracting Month from Date and doing a calculation

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
>

4. ## Re: Extracting Month from Date and doing a calculation

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43BABD23.8C6729C0@verizonXSPAM.net...
> =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
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43BABD23.8C6729C0@verizonXSPAM.net...
> =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

5. ## Re: Extracting Month from Date and doing a calculation

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

6. ## Re: Extracting Month from Date and doing a calculation

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

--

Regards,

Peo Sjoblom

"gb" <agent@agent.com> wrote in message
news:58hlr11qhlsbem50m597asnln5appubsmq@4ax.com...
> 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")

>
>

7. ## Re: Extracting Month from Date and doing a calculation

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)

8. ## Re: Extracting Month from Date and doing a calculation

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" <agent@agent.com> wrote in message
news:uijlr19r8jtvrppecb6dtmuss0gkv0oda5@4ax.com...
> 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)

>

9. ## Re: Extracting Month from Date and doing a calculation

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1