Help writing formula... multiple nested if/thens

[41roblynway]

Hi all, I am trying to create a spreadsheet that calculates allowable
(pro-rated) car rental expenses that employees can claim for a
conference that was held. The organization has agreed to pay employee
car rental expenses that fall between the dates of the conference 7th
Sept 2008 and 14th October 2008 (including both of those days).
However, some car rental contracts fall partially in this range, (as
some employees showed up early or stayed late for vacation), and
employees are reimbursed accordingly. I am trying to create a
spreadsheet that gives the number of allowable days (inside the range)
and non-allowable days (number of days outside the range) for each
employee car rental contract. Each row represents one car rental
expense.Cell E8 is the first expense start_date and cell F8 is the
first expense end_date. As I see it, there are 6 possible combinations
to calculate allowable days: (please note the dash is being used as a
subtraction sign in my formulas)

1. Expense START_DATE is before period and END_DATE is also before the
period: NO ALLOWABLE DAYS
2. START_DATE is before period and END_DATE falls within the range:
EXPENSE END_DATE - 7 SEPT 2008
3. START_DATE is before period and END_DATE falls after the period: 14
OCT 2008 - 7 SEPT 2008
4. START_DATE falls within range and END_DATE falls within range:
END_DATE - START_DATE
5. START_DATE falls within range and END_DATE falls after range: 14
OCT 2008 - START_DATE
6. START_DATE is after range and END_DATE is after range: NO ALLOWABLE
DAYS

I started experimenting with formulas at the beginning (below), but
that was before I figured out that there were more possibilities than
I had accounted for.

=IF(D8>0,(IF(E8>=DATEVALUE("7/9/2008"),((+$C$4-E8)+1),IF(E8<DATEVALUE
("7/9/2008"),(F8-$C$3)+1))),"")

Oh, and as you can see, I wanted the cell to be blank if that row had
not been filled out yet.

So, my brain hurts when I try and think about writing this formula for
all 6 possibilities, but I'm guessing there are those of you out there
that are much more skilled than I at this that could do this in their
sleep. If anyone could help, I would greatly appreciate it. Thanks,

Rob

 

[dranon]

Hi all, I am trying to create a spreadsheet that calculates allowable
(pro-rated) car rental expenses that employees can claim for a
conference that was held. The organization has agreed to pay employee
car rental expenses that fall between the dates of the conference 7th
Sept 2008 and 14th October 2008 (including both of those days).
However, some car rental contracts fall partially in this range, (as
some employees showed up early or stayed late for vacation), and
employees are reimbursed accordingly. I am trying to create a
spreadsheet that gives the number of allowable days (inside the range)
and non-allowable days (number of days outside the range) for each
employee car rental contract. Each row represents one car rental
expense.Cell E8 is the first expense start_date and cell F8 is the
first expense end_date. As I see it, there are 6 possible combinations
to calculate allowable days: (please note the dash is being used as a
subtraction sign in my formulas)

1. Expense START_DATE is before period and END_DATE is also before the
period: NO ALLOWABLE DAYS
2. START_DATE is before period and END_DATE falls within the range:
EXPENSE END_DATE - 7 SEPT 2008
3. START_DATE is before period and END_DATE falls after the period: 14
OCT 2008 - 7 SEPT 2008
4. START_DATE falls within range and END_DATE falls within range:
END_DATE - START_DATE
5. START_DATE falls within range and END_DATE falls after range: 14
OCT 2008 - START_DATE
6. START_DATE is after range and END_DATE is after range: NO ALLOWABLE
DAYS

I started experimenting with formulas at the beginning (below), but
that was before I figured out that there were more possibilities than
I had accounted for.

=IF(D8>0,(IF(E8>=DATEVALUE("7/9/2008"),((+$C$4-E8)+1),IF(E8<DATEVALUE
("7/9/2008"),(F8-$C$3)+1))),"")

Oh, and as you can see, I wanted the cell to be blank if that row had
not been filled out yet.

So, my brain hurts when I try and think about writing this formula for
all 6 possibilities, but I'm guessing there are those of you out there
that are much more skilled than I at this that could do this in their
sleep. If anyone could help, I would greatly appreciate it. Thanks,

Well, you have the basic structure correct:

=if(D8>0,[stuff],"")

Replace [stuff] with:

=MAX(0,MIN(F8,DATE(2008,10,14))+1-MAX(E8,DATE(2008,9,7)))

 

[bertbarndoor]

Normally, I would lend a hand to an individual having this kind of a
problem. But you apparently with a company that rents many cars for
weeks at a time. There should be someone in IT there that could help
you. If not, there are plenty of consultants out there.
I might even be willing to help in such a case if were something that
only needed a little bit of help. But yours was a rather long
uninteresting post.
Bill

Hi Bill, normally I would let your comments slide, but in this case
I'll make an exception. First of all, your assumptions about me are
not accurate. While I can see how your thoughts might be plausible
assumptions, there are many others that you have not taken the time to
think about. This is largely because I did not provide much detail on
my background and you really wouldn't be able to come up with much.
Moreover, I have simplified the example for the masses on the usenet.
In any case, it is neither here nor there, I just thought I'd tell you
that you are wrong and leave it at that. No one in IT, no consultants,
just me beavering away. But thanks though. I think the world needs
more people like you who make snap judgments and then proceed to offer
nothing of value to anyone. Congrats for being a zero.

 

[41roblynway]

Hi Bill, normally I would let your comments slide, but in this case
I'll make an exception. First of all, your assumptions about me are
not accurate. While I can see how your thoughts might be plausible
assumptions, there are many others that you have not taken the time to
think about. This is largely because I did not provide much detail on
my background and you really wouldn't be able to come up with much.
Moreover, I have simplified the example for the masses on the usenet.
In any case, it is neither here nor there, I just thought I'd tell you
that you are wrong and leave it at that. No one in IT, no consultants,
just me beavering away. But thanks though. I think the world needs
more people like you who make snap judgments and then proceed to offer
nothing of value to anyone.

 

[41roblynway]

Hi all, I am trying to create a spreadsheet that calculates allowable
(pro-rated) car rental expenses that employees can claim for a
conference that was held. The organization has agreed to pay employee
car rental expenses that fall between the dates of the conference 7th
Sept 2008 and 14th October 2008 (including both of those days).
However, some car rental contracts fall partially in this range, (as
some employees showed up early or stayed late for vacation), and
employees are reimbursed accordingly. I am trying to create a
spreadsheet that gives the number of allowable days (inside the range)
and non-allowable days (number of days outside the range) for each
employee car rental contract. Each row represents one car rental
expense.Cell E8 is the first expense start_date and cell F8 is the
first expense end_date. As I see it, there are 6 possible combinations
to calculate allowable days: (please note the dash is being used as a
subtraction sign in my formulas)

1. Expense START_DATE is before period and END_DATE is also before the
period: NO ALLOWABLE DAYS
2. START_DATE is before period and END_DATE falls within the range:
EXPENSE END_DATE - 7 SEPT 2008
3. START_DATE is before period and END_DATE falls after the period: 14
OCT 2008 - 7 SEPT 2008
4. START_DATE falls within range and END_DATE falls within range:
END_DATE - START_DATE
5. START_DATE falls within range and END_DATE falls after range: 14
OCT 2008 - START_DATE
6. START_DATE is after range and END_DATE is after range: NO ALLOWABLE
DAYS

I started experimenting with formulas at the beginning (below), but
that was before I figured out that there were more possibilities than
I had accounted for.

Oh, and as you can see, I wanted the cell to be blank if that row had
not been filled out yet.

So, my brain hurts when I try and think about writing this formula for
all 6 possibilities, but I'm guessing there are those of you out there
that are much more skilled than I at this that could do this in their
sleep. If anyone could help, I would greatly appreciate it. Thanks,

Well, you have the basic structure correct:

=if(D8>0,[stuff],"")

Replace [stuff] with:

=MAX(0,MIN(F8,DATE(2008,10,14))+1-MAX(E8,DATE(2008,9,7)))

Thank you sir! You have made someone happy and helped a significant
number of people out with this. Cheers to you!
-Rob

 

[dranon]

Hi all, I am trying to create a spreadsheet that calculates allowable
(pro-rated) car rental expenses that employees can claim for a
conference that was held. The organization has agreed to pay employee
car rental expenses that fall between the dates of the conference 7th
Sept 2008 and 14th October 2008 (including both of those days).
However, some car rental contracts fall partially in this range, (as
some employees showed up early or stayed late for vacation), and
employees are reimbursed accordingly. I am trying to create a
spreadsheet that gives the number of allowable days (inside the range)
and non-allowable days (number of days outside the range) for each
employee car rental contract. Each row represents one car rental
expense.Cell E8 is the first expense start_date and cell F8 is the
first expense end_date. As I see it, there are 6 possible combinations
to calculate allowable days: (please note the dash is being used as a
subtraction sign in my formulas)

1. Expense START_DATE is before period and END_DATE is also before the
period: NO ALLOWABLE DAYS
2. START_DATE is before period and END_DATE falls within the range:
EXPENSE END_DATE - 7 SEPT 2008
3. START_DATE is before period and END_DATE falls after the period: 14
OCT 2008 - 7 SEPT 2008
4. START_DATE falls within range and END_DATE falls within range:
END_DATE - START_DATE
5. START_DATE falls within range and END_DATE falls after range: 14
OCT 2008 - START_DATE
6. START_DATE is after range and END_DATE is after range: NO ALLOWABLE
DAYS

I started experimenting with formulas at the beginning (below), but
that was before I figured out that there were more possibilities than
I had accounted for.

Oh, and as you can see, I wanted the cell to be blank if that row had
not been filled out yet.

So, my brain hurts when I try and think about writing this formula for
all 6 possibilities, but I'm guessing there are those of you out there
that are much more skilled than I at this that could do this in their
sleep. If anyone could help, I would greatly appreciate it. Thanks,

Well, you have the basic structure correct:

=if(D8>0,[stuff],"")

Replace [stuff] with:

=MAX(0,MIN(F8,DATE(2008,10,14))+1-MAX(E8,DATE(2008,9,7)))

Thank you sir! You have made someone happy and helped a significant
number of people out with this. Cheers to you!

Great. Thanks for the feedback.