Help!Calculation of daily meals without knowing dates and without exceeding daily amount of 550DKK

[Jenoliv9]

Hello Excel gurus, 

I need help in creating a formula, that for me is very complex. 

So I start by explaining how the policy is: 

Our employees are only entitled to get a daily meal allowance of 550DKK. What happens is: when the employee returns from the trip (s)he includs the meals in the excel file - they include the dates when the meal occured and the amounts. 

What I need now, is a formula behind, that calculates automatically the amount that was spent for the  date (therefore the dates need to be sumed into one - so if the employee enters 2 times the same date it sums into one) and deductes on the total reimburseable amount if they exceed the 550. 

I was thinking about using a table behind with the formula (that then needs to be hidden, so the employee dosen't see it) and including the result then on the excel sheet for the total reimbursable amount. 

What is important here also, is that this should only happen if they include "meal-self" as an expense type. 

Do you have any suggestions? 

I would really appreciate. 

Thanks,

Jen

[#2]

HI@Jenoliv9 

As per my understanding first you need unique dates from a list of given dates and also sum of amounts against each unique date. And If this sum is more than 550 you want to reimburse the differential or zero otherwise.

I have tried to translate given scenario with the help of helper column in attached sample file, please let me know if it helps you.

Thanks

Tauqeer

[#3]

HI@Jenoliv9 

As per my understanding first you need unique dates from a list of given dates and also sum of amounts against each unique date. And If this sum is more than 550 you want to reimburse the differential or zero otherwise.

I have tried to translate given scenario with the help of helper column in attached sample file, please let me know if it helps you.

Thanks

Tauqeer