# I need a formula that rounds up the date.

1. ## 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. ## 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

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

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

Thank you very much the first suggestion you made worked perfectly... many thanks!

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

#### 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