Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-899232%22%20slang%3D%22en-US%22%3EHelp!Calculation%20of%20daily%20meals%20without%20knowing%20dates%20and%20without%20exceeding%20daily%20amount%20of%20550DKK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899232%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20gurus%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20in%20creating%20a%20formula%2C%20that%20for%20me%20is%20very%20complex.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20start%20by%20explaining%20how%20the%20policy%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3EOur%20employees%20are%20only%20entitled%20to%20get%20a%20%3CSTRONG%3E%3CU%3Edaily%3C%2FU%3E%3C%2FSTRONG%3E%20meal%20allowance%20of%20550DKK.%20What%20happens%20is%3A%20when%20the%20employee%20returns%20from%20the%20trip%20(s)he%20includs%20the%20meals%20in%20the%20excel%20file%20-%20they%20include%20the%20dates%20when%20the%20meal%20occured%20and%20the%20amounts.%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20now%2C%20is%20a%20formula%20behind%2C%20that%20calculates%20%3CU%3E%3CSTRONG%3Eautomatically%3C%2FSTRONG%3E%3C%2FU%3E%20the%20amount%20that%20was%20spent%20for%20the%26nbsp%3B%20date%20(therefore%20the%20dates%20need%20to%20be%20sumed%20into%20one%20-%20so%20if%20the%20employee%20enters%202%20times%20the%20same%20date%20it%20sums%20into%20one)%20%3CSTRONG%3Eand%3C%2FSTRONG%3E%20deductes%20on%20the%20total%20reimburseable%20amount%20if%20they%20exceed%20the%20550.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20thinking%20about%20using%20a%20table%26nbsp%3Bbehind%20with%20the%20formula%20(that%20then%20needs%20to%20be%20hidden%2C%20so%20the%20employee%20dosen't%20see%20it)%20and%20including%20the%20result%20then%20on%20the%20excel%20sheet%20for%20the%20total%20reimbursable%20amount.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20important%20here%20also%2C%20is%20that%20this%20should%20only%20happen%20if%20they%20include%20%22meal-self%22%20as%20an%20expense%20type.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20suggestions%3F%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20really%20appreciate.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EJen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-899232%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-899347%22%20slang%3D%22en-US%22%3ERe%3A%20Help!Calculation%20of%20daily%20meals%20without%20knowing%20dates%20and%20without%20exceeding%20daily%20amount%20of%20550D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899347%22%20slang%3D%22en-US%22%3E%3CP%3EHI%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421983%22%20target%3D%22_blank%22%3E%40Jenoliv9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20per%20my%20understanding%20first%20you%20need%20%3CSTRONG%3Eunique%20dates%3C%2FSTRONG%3E%20from%20a%20list%20of%20given%20dates%20and%20also%20sum%20of%20amounts%20against%20each%20unique%20date.%20And%26nbsp%3BIf%20this%20sum%20is%20more%20than%20550%20you%20want%20to%20reimburse%20the%20differential%20or%20zero%20otherwise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20translate%20given%20scenario%20with%20the%20help%20of%20helper%20column%20in%20attached%20sample%20file%2C%20please%20let%20me%20know%20if%20it%20helps%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-899349%22%20slang%3D%22en-US%22%3ERe%3A%20Help!Calculation%20of%20daily%20meals%20without%20knowing%20dates%20and%20without%20exceeding%20daily%20amount%20of%20550D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899349%22%20slang%3D%22en-US%22%3E%3CP%3EHI%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421983%22%20target%3D%22_blank%22%3E%40Jenoliv9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20per%20my%20understanding%20first%20you%20need%20%3CSTRONG%3Eunique%20dates%3C%2FSTRONG%3E%20from%20a%20list%20of%20given%20dates%20and%20also%20sum%20of%20amounts%20against%20each%20unique%20date.%20And%26nbsp%3BIf%20this%20sum%20is%20more%20than%20550%20you%20want%20to%20reimburse%20the%20differential%20or%20zero%20otherwise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20translate%20given%20scenario%20with%20the%20help%20of%20helper%20column%20in%20attached%20sample%20file%2C%20please%20let%20me%20know%20if%20it%20helps%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jenoliv9
Visitor

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 Replies

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

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies