TODAY
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: TODAY

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    East Sussex
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello,

    I am trying to use the TODAY function to calculate monthly sales based on sales to date, i.e. if today is the 16th, I want to take total sales month-to-date, and extend it to 30/16ths of the sales total.

    Any ideas?

    Nanette

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you could use
    =(DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0)))/DAY(NOW())

    as your multiplier. now() used instead of today() to reduce carpel tunnel.

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    East Sussex
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you for your prompt reply. The formula worked well. Now that it is working, I am told that our MTD sales are to YESTERDAY, not today.

    How can I modify this formula to calculate yesterday's factor?

    NW

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =(DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0)))/(DAY(NOW())-1)

    should do the trick

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-16 11:42, scwyj wrote:
    Thank you for your prompt reply. The formula worked well. Now that it is working, I am told that our MTD sales are to YESTERDAY, not today.

    How can I modify this formula to calculate yesterday's factor?

    NW
    This formula calculates your Projected Monthly Sales based on MTD Sales (in cell A1) thru yesterday...

    =TREND({0,1}*A1,{0,1}*DAY(TODAY()-1),DAY(EOMONTH(TODAY(),0)))

    Note: This formula uses the EOMONTH function which is supplied by the Analysis ToolPak. See the Excel Help topic for "Install and use the Analysis ToolPak" if you need to install this add-in.

    [ This Message was edited by: Mark W. on 2002-04-16 13:52 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com