CALCULATE DUE DATE & RESTART DATE

[zen]

I need to calculate due after adding certain number of months and days to a
given date. A1 is having a date say 21-Jun-08, in A2 I will enter 12 months &
in A3 I will enter 1 day. In A4 I need calculated due date after 12 months &
1 day, which is 21-Jun-09.Then finally a calculated restart date in A6 after
a shutdown of 12 days, these 12 days will be entered in A5. XYZ company will
complete its operating period of 12M & 1D on 21-Jun-09 and will shut down on
22-Jun-09 to restart on 3-Jul-09 after 12 shutdown days.
It took me long to explain but I need this at my job. Any help will be
highly appreciate.

 

[Huber57]

Zen,

Correct me if I am wrong but I think your due date will be 22-Jun-09.

Anyway, try this.

In A4, type =A1+((365/12)*A2)+A3
This should return a number. Go to Format Cells (Control+1) and change the
format to a date.

This should be 6/22/2009.

In A6, type =A4+A5. This will add your end date to your shut down time.

The result should be: 7/4/2009

Hope that helps.

 

[JLatham]

For this solution, you need the Analysis ToolPak installed. To make sure it
is installed, use:
Tools | Options | Add Ins
and make sure the box next to Analysis ToolPak is checked.

A1: Initial Date as 7/21/08
A2: Months as 12
A3: Days as 1
A4: Formula-- =EDATE(A1,A2)+A3
That will actually give you 7/22/09 in A4, not 7/21/09 as you indicated. If
you don't add the 1 day from A3 to it, it comes up with 7/21/09.
A5: shutdown days as 12
A6: Formula-- =A4+A5
which will give you 8/3/2008.

We seem to be 1 month off in the results, so you probably want to enter 11
into A2 instead of 12, that is time period in months minus 1 month. We could
actually adjust that in the formula at A4 as:
=EDATE(A1,A2-1)+A3
so that people could enter 12 'naturally' without having to do any
conversion in their heads.

HTH,
JLatham

 

[JLatham]

Oops, my bad - no adjustment needed as I'd entered JULY 21 08 as the start
date, not JUNE 21. So original formula is good in A4.

 

[Sandy Mann]

I don't get 21-Jum-09 for adding 12 months and one day but try:

A4: =DATE(YEAR(A1),MONTH(A1)+A2,DAY(A1)+A3)

A5: =A4+12

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk