+ Reply to Thread
Results 1 to 5 of 5

formula for month that is prior to actual

  1. #1
    braadi
    Guest

    formula for month that is prior to actual

    i generate reports for data collected from the previous month (i.e. in
    december, i report for november's data). how do i formulate in my reports
    the date to show the previous month and year (while automatically updating
    according to excel date and time)?

  2. #2
    Peo Sjoblom
    Guest

    Re: formula for month that is prior to actual

    One way

    =TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"MMM YYYY")



    --

    Regards,

    Peo Sjoblom


    "braadi" <braadi@discussions.microsoft.com> wrote in message
    news:7E883E6D-8063-47A9-9AD1-D19B028A676E@microsoft.com...
    > i generate reports for data collected from the previous month (i.e. in
    > december, i report for november's data). how do i formulate in my reports
    > the date to show the previous month and year (while automatically updating
    > according to excel date and time)?




  3. #3
    TedMi
    Guest

    RE: formula for month that is prior to actual

    =DATE(IF(MONTH(NOW())=1,YEAR(NOW())-1,YEAR(NOW())),
    IF(MONTH(NOW())=1,12,MONTH(NOW())-1), 28)
    This might be easier to follow if you store intermediate results in cells:
    A1: =IF(MONTH(NOW())=1,12,MONTH(NOW())-1)
    A2: =IF(A1=12,YEAR(NOW())-1, YEAR(NOW)))

    =Date(A2, A1, 28)

    This assumes that you put the DATE function in a cell formatted to show
    mm/yyyy. In that case, the day arg to the DATE function is arbitrary and can
    be any day number which appears in every month (i.e. 1-28).
    --
    Ted


  4. #4
    braadi
    Guest

    RE: formula for month that is prior to actual

    great. thanks so much.

    "TedMi" wrote:

    > =DATE(IF(MONTH(NOW())=1,YEAR(NOW())-1,YEAR(NOW())),
    > IF(MONTH(NOW())=1,12,MONTH(NOW())-1), 28)
    > This might be easier to follow if you store intermediate results in cells:
    > A1: =IF(MONTH(NOW())=1,12,MONTH(NOW())-1)
    > A2: =IF(A1=12,YEAR(NOW())-1, YEAR(NOW)))
    >
    > =Date(A2, A1, 28)
    >
    > This assumes that you put the DATE function in a cell formatted to show
    > mm/yyyy. In that case, the day arg to the DATE function is arbitrary and can
    > be any day number which appears in every month (i.e. 1-28).
    > --
    > Ted
    >


  5. #5
    braadi
    Guest

    Re: formula for month that is prior to actual

    i appreciate the help so much. look forward to getting this working now.

    "Peo Sjoblom" wrote:

    > One way
    >
    > =TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),0),"MMM YYYY")
    >
    >
    >
    > --
    >
    > Regards,
    >
    > Peo Sjoblom
    >
    >
    > "braadi" <braadi@discussions.microsoft.com> wrote in message
    > news:7E883E6D-8063-47A9-9AD1-D19B028A676E@microsoft.com...
    > > i generate reports for data collected from the previous month (i.e. in
    > > december, i report for november's data). how do i formulate in my reports
    > > the date to show the previous month and year (while automatically updating
    > > according to excel date and time)?

    >
    >
    >


+ 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