+ Reply to Thread
Results 1 to 4 of 4

I need a formula that rounds up the date.

  1. #1
    Registered User
    Join Date
    08-20-2012
    Location
    Jersey, Channel Islands
    MS-Off Ver
    Office 2010
    Posts
    25

    I need a formula that rounds up the date.

    Hi All

    I am in desperate need of a formula that rounds dates up for me. For example, if I have a date in D2 of 30/09/2011 I want it so that in C2 I get 31/12/2011. I need to be able to do this for a series of dates ranging from 01/01/2005 to 31/01/2013 , adding up to nearly a thousand records, as you can imagine, it would be incredibly inefficient for me to do this by hand.

    Here's hoping someone out there can help.

    Many Thanks,
    Martin

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: I need a formula that rounds up the date.

    I have to guess what you are trying to do.

    I assume you want to start with the last day of the month and add three months. Is that right? If so this will work:

    Assume the Date is A1
    =DATE(RIGHT(A1,4),MID(A1,4,2)+4,1)-1
    Click on star (*) below if this helps

  3. #3
    Valued Forum Contributor
    Join Date
    04-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    547

    Re: I need a formula that rounds up the date.

    Describe the logic in simple words. Why should 30/09/2011 be changed to 31/12/2011?? Do you want to change the date to

    - the last day of the year? Then =date(Year(A1),12,31)

    - some other date? If so, you need to describe the logical rules that lead to the desired result.
    Like a post? Click the star below it!

  4. #4
    Registered User
    Join Date
    08-20-2012
    Location
    Jersey, Channel Islands
    MS-Off Ver
    Office 2010
    Posts
    25

    Re: I need a formula that rounds up the date.

    Thank you very much the first suggestion you made worked perfectly... many thanks!
    Last edited by Cutter; 08-21-2012 at 07:24 PM. Reason: Removed whole post quote

+ 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