Automatically Calculate R & R Schedule in Excel 07

[akrshaw]

I am trying to automatically calculate the number of R and R trips our guys get based on their mobilization and demobilization dates.

Then produce what those dates would be.

I can calculate the number of days between the mob and demob no problem.

This is what I am trying to accomplish without having to manually calculate it.

With the below table they would get 1 R&R for every 4 months worked/120 days, then get 14 days R&R.

Name Mob Date Demob Date # of R & R First R & R Second R & R
Doe, John 1/1/2009 1/1/2010 2 4/30/2009 9/11/2009

Any ideas? Would really rather not put into Access, users have limited access to the program...company locks it down.

Thanks!~

 

[AnotherHiggins]

I have no idea what you're asking for.

->"With the below table they would get 1 R&R for every 4 months worked/120 days, then get 14 days R&R.
What does that mean?

1 R&R.... days?
4 months/120 days.... So doesn't depend on hours - or even days - worked?
then get 14 days R&R.... But your example only shows 2 days total.

Please back up, remind yourself that your readers don't know any background about what you're trying to accomplish, and try to explain your requirements again.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[akrshaw]

Sooo, sorry...I was trying to give enough information without overload.

They guys go out on assignment, they get 14 days of R&R for every 4months/120 days worked.

It is not based on hours worked it is based on days mobilized.

Every time they come back the counter starts over til 120 days and their next 14 day R & R.

So, based on the dates in the example the John Doe would be mobilized for 1 year/365 days.

If you take the 120 then add the 14 the first R & R would be in April. And he would only get 2 R & R's for the year.

Ultimately I am trying to say if you are mobilized x many days, then you work 120 days for every R&R, and x is how many you get for 365 days and this is the projected dates for those R&R's.

 

[SkipVought]

Hi,

I assumed that your table starts in column A, row 1.

I inserted 2 rows above your table, so now your table starts in column A row 3.

In row 1 starting in column E, two rows above First R & R, I have 1 2 3 in columns E, F & G. If there might be more R&Rs then continue this numbering.

Here's the formula in D4 & E4...
[tt]
D4: =(C4-B4)/120
E4: =IF(AND($D4>E$1,$B4+E$1*120+14*D$1-1<=$C4),$B4+E$1*120+14*D$1-1,"")
[/tt]
My results...
[tt]
Doe, John 1/1/2009 1/1/2010 3.041666667 4/30/2009 9/11/2009
[/tt]
Using named ranges...
[tt]
D4: =(Demob_Date-Mob_Date)/120
E4: =IF(AND(of_R___R>E$1,Mob_Date+E$1*120+14*D$1-1<=Demob_Date),Mob_Date+E$1*120+14*D$1-1,"")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[akrshaw]

Skip -

We JUST today moved to 07, when I type in your formula for D4 it only shows it as text not a formula.

Am I missing something?

D8 = =(C8-B8)/120

Apparently I am not hitting the magic new button.

 

[akrshaw]

FOUND the MAGIC button!~

Just saw the check mark...they should really tell a person...grin

 

[AnotherHiggins]

Ah, 2007. Say no more....

I found these to be very helpful when we first made the switch:

http://office.microsoft.com/en-us/training/HA102295841033.aspx

At that site are interactive guides that allow you to click on the menu structure you're used to from 2003, and then it will show you where that command is in 2007's ribbons.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[akrshaw]

Good Morning!~

I hope everyone had a GREAT weekend!~

I tried the formula that Skip provided above:

=IF(AND(D8>E$1,B8+E$1*120+14*D$1-1<=C8),B8+E$1*120+14*D$1-1,"")

Last Name First Name Mob_Date Demob_Date # of R&R First R&R
Doe John 1/1/2009 1/1/2010 3.04 12/31/2008

I end up getting 12/31/2008 as the response...

I'm sure its because I have missed something?

 

[SkipVought]

In row 1 starting in column E, two rows above First R & R, I have 1 2 3 in columns E, F & G. If there might be more R&Rs then continue this numbering.

Did you forget this?

The result...
[tt]
1

Name Mob_Date Demob_Date # of R&R First R&R
Doe, John 1/1/2009 1/1/2010 3.041666667 4/30/2009
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

There are actually TWO R & R dates calculated...
[tt]
4/30/2009 9/11/2009
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[akrshaw]

The light has gone off in the bubble over my head!~

Thank you sooo very much, lately 2 + 2 hasn't equaled 4...and I forgot my address last week!~

Thank you for the reminder!~

 

[SkipVought]

If you're on SECOND BASE, 1 PLUS 1 EQUALS 10.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}