• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I have a complicated function that I need to perform in a rather large spreadsheet that I am hoping someone can help me with.

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. :)
 

Attachments

  • ForumCopy 01012020.xlsm
    148.9 KB · Views: 15
Kristen_cancerkiller
Your sample file's employee has marked only 'Date' B14 ... and there seems to come all meals ... how?
If overnight or more longer travels, then how to mark?
Your writings above and Your sample should be same, that could follow Your needs.
 
One formula that may help is one to calculate the overlap between two time intervals

64778

Even the SUMPRODUCT is unusual in that it operates over 2D arrays.
 
Last edited:
Kristen_cancerkiller
Your sample file's employee has marked only 'Date' B14 ... and there seems to come all meals ... how?
If overnight or more longer travels, then how to mark?
Your writings above and Your sample should be same, that could follow Your needs.


Hi vletm,

You are right... the file that I submitted is my practice copy that I have been testing. I wasn't quite finished getting the meals to work but I didn't want to remove the formulas to send the fully complete file. I have since gotten that part fixed. :)

If the travel is overnight, the employee must 'add' a new line for each day... they simply use the depart time as 12:01 am if they stayed the night before and/or return time of 12:00 pm if they are staying overnight that night. I would love to be able to set this up to allow multiple days entries in one but I am still just a newbie at this process and that is beyond me still. Once I have employees using the form the way it is, I will continue to work to improve it but I don't want to wait 6 months to have employees use this as the process we have now is very confusing and convoluted.

Currently, employees are using the spreadsheet and entering each day in the same way, they just don't have the benefit of the userform nor the calculations that will now be provided. This document will calculate expense codes and everything as well as tell them what they can and cannot claim. This in itself is a huge step of improvement over what they have to do now. :)

Thank you for your comments and suggestions.
 
Last edited:
One formula that may help is one to calculate the overlap between two time intervals

View attachment 64778

Even the SUMPRODUCT is unusual in that it operates over 2D arrays.

Hi Peter,

I have no idea how to do this. I'm not opposed but I don't know what you did here. Do you have the excel document that I could use to follow your process and try it in my document?

I had thought I could do it using the morning and evening meals and determining then if they had been in travel status long enough to also get the midday meal but I was wrong. There are times that the employee will only get the midday meal and I can't depend on the morning and evening meals for calculation (duh). So back to the drawing board. :) This is my last complicated formula/function that I will have to do I think before I complete this document and it is ready to send on for testing by others. I would really like to get this done over the weekend to come... so any suggestions and assistance will be greatly appreciated. :)
 
Kristen_cancerkiller
Isn't that 'extra job', if have to make two lines if have overnight travelling or even longer ... for me travelling starts and ends
... why to 'stop' for one minute at midnight?
Without something really clear, it will be a challenge for me.
 
Kristen
Please find a copy of my spreadsheet attached. Your first thoughts may well be "Is this Excel?" or "What planet does he come from?"
It is Excel (just not as you know it) but, with the documentation block, it should make sense once the shock has worn off.

Note: In use the 'travelPeriod' will need to be row-relative to apply to all your data but then the formulas must be from the same row.
 

Attachments

  • overlapping times intervals.xlsx
    23.7 KB · Views: 6
Kristen
Please find a copy of my spreadsheet attached. Your first thoughts may well be "Is this Excel?" or "What planet does he come from?"
It is Excel (just not as you know it) but, with the documentation block, it should make sense once the shock has worn off.

Note: In use the 'travelPeriod' will need to be row-relative to apply to all your data but then the formulas must be from the same row.

WOW!! This is way over my head. LOL But Ima risk it. :) You mentioned that the 'travelPeriod' will need to be row-relative, that I can do but can I put all of these pieces into the same row or does it need to be 2rows and 2columns as you have each piece? Also, can any of the pieces be on a separate hidden worksheet? I'm trying to keep some of the pieces on a hidden 'codes' worksheet so staff won't end up breaking it. :) I'm willing to try it... I just want to do it right. :) Please let me know!



Kristen_cancerkiller
Isn't that 'extra job', if have to make two lines if have overnight travelling or even longer ... for me travelling starts and ends
... why to 'stop' for one minute at midnight?
Without something really clear, it will be a challenge for me.

I agree that it is extra work but I do not know how to program it so it would calculate multiple meals for multiple days. I'm not unwilling to learn but I need to put this into effect soon as the process now in place is very broken. I do not object to improvements in time but as you can see, with attempting to calculate the meals also. The meals and mileage and everything must calculate as well as an additional 'calculation' to determine the correct expense codes that will be used. This document is for individuals who are very smart but do not necessarily have the computer knowledge and/or time to do all of the computations and calculations on their own. Currently, we struggle with getting complete info because staff is too busy to take a lot of time to fill these out so I am attempting to do the calculations and computations for them so they only have to complete the bare minimum of info. Do you have a suggestion of how to fix it and not have to do it day by day? I'm open to any solutions anyone might have that will improve the form.

As I said, currently staff is completing the info on the spreadsheet manually including the meal calculations and expense code determinations also and they are entering one line for each day traveled. I simply have them using the midnight and 12:01 times to attempt to make it clear for them and to provide cleaner information. Also, certain information is required of staff that must be entered on different lines for each day traveled. If the information isn't provided, then we cannot reimburse them for their expenses. Currently, they complete a separate line manually for each day that they travel, so I don't think it will be an issue for them to do it this way since there is less info for them to provide and less calculations for them to do. So they are, in fact, completing a lot less information than they currently do, so it is a win-win.

This document is going to also decrease the amount of processing time for the payroll staff by at least 75% as the majority of processing will already be done. There are some give and takes in all improvements but these have been weighed to ensure that it will be beneficial in the end.
 
Last edited:
Kristen_cancerkiller
Seems that You're hurry now and You try to do this ASAP - that's okay - You'll use that.
It's good that You try improve Your document as well as it's good that You have asked even 'hints' how to do it. Your total 75% will be a lot!
If You would have time to try to modify steps which would be like 'extra' then there would be less steps which that 'form' needs now.
I know what is 'busy' ... many times it means shortcuts. Have You heard: 'measure twice and cut once' ?
I could have suggestion, but as I tried to write ... I need something really clear.
If 'just' change one part, there could be 'interesting side-effects' somewhere.
Your named 'meal-calculation' isn't 1+2+3= ... hmm? ... 6.
To verify, that any modifications will work,
it would be good to test with data (which covers the most of cases), which results has verified before that.
 
WOW!! This is way over my head.
Please do not feel badly about it! It is me, not you. For most users array formulas are either unheard of or, at best, methods of last resort. For me, they provide a starting point and I only break arrays down into single cell formulas where essential. A further difference is that I start with the belief that ALL references should be named, sheet locations such as PQ123 are a matter of chance or, perhaps, graphic design and have no relevance to the problem being solved.

This combination of ideas means that I create 'alien' solutions that are understood by the experts on this site but may not serve the needs of basic users (I have wondered about consulting @Hui to see whether he thinks I am causing more confusion than insight through my contributions!)

In the present instance, the 2D arrays are shown merely for documentation purposes; they all may be deleted, leaving only a single cell showing the qualifying hours worked. Since the formula happens to be SUMPRODUCT, it performs the necessary array calculations without the user needing to commit the formula using CSE (Note: in the latest versions of Excel all calculations are arrays by default). In the attached I have used name manager to redefine the name 'travelPeriod' to refer to a mixed reference range i.e. specific columns on the current active row. I have illustrated this using a chart to show the 6 possible positions of a travel time period relative to the qualifying period. The trick is to take care of all 6 situations without creating a nest of IF statements.

Good luck.
 

Attachments

  • overlapping times intervals (graphics).xlsx
    27.5 KB · Views: 7
Please do not feel badly about it! It is me, not you. For most users array formulas are either unheard of or, at best, methods of last resort. For me, they provide a starting point and I only break arrays down into single cell formulas where essential. A further difference is that I start with the belief that ALL references should be named, sheet locations such as PQ123 are a matter of chance or, perhaps, graphic design and have no relevance to the problem being solved.

This combination of ideas means that I create 'alien' solutions that are understood by the experts on this site but may not serve the needs of basic users (I have wondered about consulting @Hui to see whether he thinks I am causing more confusion than insight through my contributions!)

In the present instance, the 2D arrays are shown merely for documentation purposes; they all may be deleted, leaving only a single cell showing the qualifying hours worked. Since the formula happens to be SUMPRODUCT, it performs the necessary array calculations without the user needing to commit the formula using CSE (Note: in the latest versions of Excel all calculations are arrays by default). In the attached I have used name manager to redefine the name 'travelPeriod' to refer to a mixed reference range i.e. specific columns on the current active row. I have illustrated this using a chart to show the 6 possible positions of a travel time period relative to the qualifying period. The trick is to take care of all 6 situations without creating a nest of IF statements.

Good luck.

You are amazing!! You should definitely consider consulting. :) But please not until I have this complete and maybe you would be willing to check to see if I have done it correctly. :) I believe that what you are suggesting is very likely to work and I am not doubting you at all... I only doubt myself and my abilities to make it work properly. But if you are willing to continue to assist if I have further questions, I KNOW this can be a successful solution to my issue. I am going to work on this today and will post again if I have any further questions or issues. Thank you so much, Peter! You are a rockstar in my book. :)
 
You are amazing!! You should definitely consider consulting. :) But please not until I have this complete and maybe you would be willing to check to see if I have done it correctly. :) I believe that what you are suggesting is very likely to work and I am not doubting you at all... I only doubt myself and my abilities to make it work properly. But if you are willing to continue to assist if I have further questions, I KNOW this can be a successful solution to my issue. I am going to work on this today and will post again if I have any further questions or issues. Thank you so much, Peter! You are a rockstar in my book. :)

I think we're getting really close. I have named each group of fields accordingly, and I have inserted the trip information and the formula but am getting a 'value' error on the overlap calculation. Could you please look at my new file and tell me what blaring error I must have made? :) I have no doubt that I 'oopsed' somewhere, I'm just not completely sure where. :) Thank you so much! Also, how did you get your TravelPeriod to allow for the new fields as you moved down rows? I have tried that and am having no luck? This has been a '3-month in the making' document and I'm really getting tired of working on it. I am not sure what I've missed but I will continue to see if I can figure it out as well but if you have any suggestions, I would be so greatly appreciative! Thank you!
 

Attachments

  • ForumCopy 01012020.xlsm
    170.5 KB · Views: 7
Broadly, you were there. The dates in the first columns were text rather than numbers so the inequalities did not work.
The overlap time will need number formatting [h]:mm to avoid losing hours in excess of 24 and showing am/pm.
 

Attachments

  • ForumCopy 01012020.xlsm
    87.1 KB · Views: 3
Thank you so much, Peter!!! You are my hero. :) I only have a couple more things to finish and I think I can send this out for testing by coworkers. :) Thank you so much again for your patience and helping me with this. If I knew how to give good recommendations to your assistance, I would. But I'm still new here and haven't learned if that is a thing. But please know that you have helped me greatly!!
 
Back
Top