Kristen_cancerkiller
Member
I am creating a document that determines if employees are allowed specific meals based on several different times. I have the morning meal and evening meal as I could use a set time for excel to look at, however, for the midday meal, there are actually four times that must be considered. First, excel needs to determine, from the travel times entered by the employee, if the employee was in travel at any time between the hours of 10:01 a.m. and 3:00 p.m.. Then, excel also needs to determine if they were in travel status for at least 3 hours between those times. For example, an employee may put down that they left at 6:30 a.m. and returned at 2:30 pm. So excel needs to determine first, that the employee did in fact travel between the allotted times, and second, that the employee was in travel status for at least 3 hours (3/24) during that time frame. This will then allow me to program my document to give them the meal per diem allowances that they are eligible to receive I do not even know where to start.
The morning meal was similar in that they had to be in a travel status for at least 3 hours (3/24) before 10:00 a.m. which I was able to set up by using a set time of 10:00 and subtract the time they left, and then determined that they had been in travel status for 3 hours (when they left at 6:30 a.m.). The evening meal was also similar in that they had to be in a travel status for at least 3 hours (3/24) after 3:01 p.m. which I was able to set up by using a set time of 3:01 and subtract that from the time that they returned to determine if they had been in travel status for the 3 hours required (which they weren't as they returned at 3:00 p.m. in the example above).
So my dilemma then is the midday meal where I need to determine if the employee was in travel status for 3 full hours between 10:01 a.m. and 3:00 p.m. as stated above. Is there a way to tell excel to look at the times between set times and the times traveled to determine if they are eligible for the meal? I am using a userform to setup data entry but these calculations are simply formulas and functions on the spreadsheet where the data is populated from the userform entry. I am not doing these formulas, etc. through vba as I wasn't sure how to code that. If it would be easier in vba and someone can tell me how to do so, I would be thrilled to move it to that method instead as I feel vba works so much smoother and more seamless that the functions and formulas on a document that many people will be using.
I have attached a copy of the document with business info removed. You will see on the 'Travel Expense Voucher' tab, additional columns to the far right with my formulas and working info. I have tried to figure this out now for several weeks and would appreciate any help that anyone can give. Again, as I said earlier, if I could do this through vba that would be preferred but I don't know that it would work properly as I do not know how to code that piece?
Thank you so much in advance... I really hope someone can help me.
The morning meal was similar in that they had to be in a travel status for at least 3 hours (3/24) before 10:00 a.m. which I was able to set up by using a set time of 10:00 and subtract the time they left, and then determined that they had been in travel status for 3 hours (when they left at 6:30 a.m.). The evening meal was also similar in that they had to be in a travel status for at least 3 hours (3/24) after 3:01 p.m. which I was able to set up by using a set time of 3:01 and subtract that from the time that they returned to determine if they had been in travel status for the 3 hours required (which they weren't as they returned at 3:00 p.m. in the example above).
So my dilemma then is the midday meal where I need to determine if the employee was in travel status for 3 full hours between 10:01 a.m. and 3:00 p.m. as stated above. Is there a way to tell excel to look at the times between set times and the times traveled to determine if they are eligible for the meal? I am using a userform to setup data entry but these calculations are simply formulas and functions on the spreadsheet where the data is populated from the userform entry. I am not doing these formulas, etc. through vba as I wasn't sure how to code that. If it would be easier in vba and someone can tell me how to do so, I would be thrilled to move it to that method instead as I feel vba works so much smoother and more seamless that the functions and formulas on a document that many people will be using.
I have attached a copy of the document with business info removed. You will see on the 'Travel Expense Voucher' tab, additional columns to the far right with my formulas and working info. I have tried to figure this out now for several weeks and would appreciate any help that anyone can give. Again, as I said earlier, if I could do this through vba that would be preferred but I don't know that it would work properly as I do not know how to code that piece?
Thank you so much in advance... I really hope someone can help me.
Attachments

148.9 KB Views: 15