+ Reply to Thread
Results 1 to 9 of 9

Todays date minus 1 month

  1. 11-20-2008, 12:20 PM #1
    Newton1234
    Newton1234 is offline
    Registered User
    Join Date
    12-17-2007
    Posts
    38

    Todays date minus 1 month

    Hi,

    I am trying to write a formula which takes todays date and goes back 1 month.

    Eg Today = 20/11/2008, however my formula would give me 20/10/2008

    I have tried 
    Please Login or Register  to view this content.
    However this just minuses a day.

    Any help is appreciated.

    Thanks
    Last edited by Newton1234; 11-20-2008 at 01:28 PM.
    Reply With QuoteRegister To Reply
  2. 11-20-2008, 12:35 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Try:

    =Date(Year(Today()),Month(Today())-1,Day(Today())
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 11-20-2008, 12:37 PM #3
    Newton1234
    Newton1234 is offline
    Registered User
    Join Date
    12-17-2007
    Posts
    38
    That is great thanks.

    Would it be possible to just pick out the month ? Eg, if I am doing todays date minus one month, it would bring back 10 (or October) ?

    Thanks
    Reply With QuoteRegister To Reply
  4. 11-20-2008, 12:41 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    To get just the number 10 for October... you can just use =Month(Today())-1

    To get the month name...

    =Text(Date(Year(Today()),Month(Today())-1,Day(Today()),"MMMM")
    Reply With QuoteRegister To Reply
  5. 11-20-2008, 12:46 PM #5
    Newton1234
    Newton1234 is offline
    Registered User
    Join Date
    12-17-2007
    Posts
    38
    Thanks, it might be a little easier to have the word of the month, however the formula gives me an error of too many arguments and highlights the end "MMMM" part.

    Your help is really appreciated.
    Reply With QuoteRegister To Reply
  6. 11-20-2008, 12:50 PM #6
    dominicb
    dominicb is offline
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Good afternoon Newton1234

    You could invoke the Analysis ToolPak add-in (Tools > Add-ins) and use this formula :
    =EDATE(NOW(),-1)

    The just format your cell using custome format to MMMM (or MMM for abbreviated months).

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.
    Reply With QuoteRegister To Reply
  7. 11-20-2008, 12:59 PM #7
    Newton1234
    Newton1234 is offline
    Registered User
    Join Date
    12-17-2007
    Posts
    38
    Hi, thanks for your help.

    I have tried this but I just get an error of #Name?

    I selected a custom format for the cell of MMMM.

    Any help would be fab.
    Reply With QuoteRegister To Reply
  8. 11-20-2008, 01:07 PM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by Newton1234 View Post
    Thanks, it might be a little easier to have the word of the month, however the formula gives me an error of too many arguments and highlights the end "MMMM" part.

    Your help is really appreciated.

    Forgot a parenthis...

    =TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),"MMMM")

    As for Dominic's formula, you need to install the Analysis Toolpak.. throught Tools|Addins
    Reply With QuoteRegister To Reply
  9. 11-20-2008, 01:28 PM #9
    Newton1234
    Newton1234 is offline
    Registered User
    Join Date
    12-17-2007
    Posts
    38
    Thank you very much, that has worked great :-)

    Much appreciated
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1