Date Comparison Formula

[mlv]

Looks like I need some help, please:

I have a column (A1:A12) of dates representing the twelve months of a
company year (e.g. A1 = April 2007 to A12 = March 2008).

In the second column (B1:B12) is a 'cumulative amount due' currency figure
representing the sum to date of the monthly expenses claimed by an employee
over the company year.

The third column (C1:C20) contains the various dates that expenses were
reimbursed to the employee.

The fourth column (D1-D20) contains a record of the expense amounts
reimbursed to the employee over the year. These reimbursements are random
and not necessarily on a monthly basis. There might be more than one
reimbursement in a month, or there might not be any. The number of
reimbursements could exceed twelve in the year, hence using D120 for this
column.

In the fifth column (E1:E20) I want to show the 'balance of expenses due'
(or overpaid) to the employee at the date the reimbursement was made.

What I would like to do is calculate the 'balance of expenses due' based on
the date that the reimbursement was made (C1:C20), using the 'cumulative
amount due' figure (B1:B12) for the same year & month.

Example: If a reimbursement was made on 18 June 2007 (assume reimbursement
date entered in cell C4 and reimbursement amount entered in cell D4) , then
the amount reimbursed to date (sum cells D14) should be subtracted from
the 'cumulative amount due' figure in column B1:B12 that corresponds to June
2007 (June 2007 would be in cell A3 and the corresponding 'cumulative amount
due' would be in cell B3).

Therefore the sum would be (B3-SUM(D14)). The answer should appear in
cell E4

How easy is it to compare the year and month of the reimbursement date with
the dates in column A1:A12, find the year/month match and use the
corresponding 'cumulative amount due' in the calculation?

Is this a task for VLOOKUP? I've tried VLOOPUP, but keep getting a #REF!
error.

Thanks

 

[Jan Carlo]

Hello!

i didn't quite understood your explanation, but may I suggest combining the
Vlookup and the formula month. sorry I cant be of greater help but i you
play a little with the formulas ull get it right!

 

[pub]

Looks like I need some help, please:

I have a column (A1:A12) of dates representing the twelve months of a
company year (e.g. A1 = April 2007 to A12 = March 2008).

In the second column (B1:B12) is a 'cumulative amount due' currency
figure representing the sum to date of the monthly expenses claimed by
an employee over the company year.

The third column (C1:C20) contains the various dates that expenses
were reimbursed to the employee.

The fourth column (D1-D20) contains a record of the expense amounts
reimbursed to the employee over the year. These reimbursements are
random and not necessarily on a monthly basis. There might be more
than one reimbursement in a month, or there might not be any. The
number of reimbursements could exceed twelve in the year, hence using
D120 for this column.

In the fifth column (E1:E20) I want to show the 'balance of expenses
due' (or overpaid) to the employee at the date the reimbursement was
made.

What I would like to do is calculate the 'balance of expenses due'
based on the date that the reimbursement was made (C1:C20), using the
'cumulative amount due' figure (B1:B12) for the same year & month.

Example: If a reimbursement was made on 18 June 2007 (assume
reimbursement date entered in cell C4 and reimbursement amount entered
in cell D4) , then the amount reimbursed to date (sum cells D14)
should be subtracted from the 'cumulative amount due' figure in column
B1:B12 that corresponds to June 2007 (June 2007 would be in cell A3
and the corresponding 'cumulative amount due' would be in cell B3).

Therefore the sum would be (B3-SUM(D14)). The answer should appear
in cell E4

How easy is it to compare the year and month of the reimbursement date
with the dates in column A1:A12, find the year/month match and use the
corresponding 'cumulative amount due' in the calculation?

Is this a task for VLOOKUP? I've tried VLOOPUP, but keep getting a
#REF! error.

Thanks

it would have helped to see your vlookup to see what the problem was, but
vlookup should work
copy & paste this in cell e4

=VLOOKUP(C4,$A$1:$B$12,2)-SUM($D$14)

then you can copy& paste up and down column E

 

[mlv]

it would have helped to see your vlookup to see what the problem
was, but vlookup should work copy & paste this in cell e4

=VLOOKUP(C4,$A$1:$B$12,2)-SUM($D$14)

then you can copy& paste up and down column E

Actually my VLOOKUP formula wouldn't have helped you much at all because to
write the original question with any clarity, I had to greatly simplify the
cell references. Even then, it was difficult for someone else to follow
what I was trying to do. My actual table contains many merged cells.

The good news is that having studied your example, I could see where I had
gone wrong. I had specified the table incorrectly.

My final formula (which appears to work perfectly) is:

=IF(ISERROR(VLOOKUP(L6,$C$6:$J$29,8)-SUM($N$6:O6)),"",VLOOKUP(L6,$C$6:$J$29,8)-SUM($N$6:O6))

I've used the ISERROR function to hide the error message when there are rows
with only partial information entered.

Thanks for your help.

 

[pub]

Actually my VLOOKUP formula wouldn't have helped you much at all
because to write the original question with any clarity, I had to
greatly simplify the cell references. Even then, it was difficult for
someone else to follow what I was trying to do. My actual table
contains many merged cells.

The good news is that having studied your example, I could see where I
had gone wrong. I had specified the table incorrectly.

My final formula (which appears to work perfectly) is:

=IF(ISERROR(VLOOKUP(L6,$C$6:$J$29,8)-SUM($N$6:O6)),"",VLOOKUP(L6,$C$6:$
J$29,8)-SUM($N$6:O6))

I've used the ISERROR function to hide the error message when there
are rows with only partial information entered.

Thanks for your help.

good that it worked. i think you were right, and your description
without the formula turned out to be the best way. the mere mention of
"merged cells" would have had me running for the hills )