+ Reply to Thread
Results 1 to 9 of 9

Extracting Month from Date and doing a calculation

  1. #1
    gb
    Guest

    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. #2
    Dave Peterson
    Guest

    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. #3
    CLR
    Guest

    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. #4
    Sandy Mann
    Guest

    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. #5
    gb
    Guest

    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. #6
    Peo Sjoblom
    Guest

    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. #7
    gb
    Guest

    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. #8
    Peo Sjoblom
    Guest

    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. #9
    Dave Peterson
    Guest

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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