hello, there is an equasion i made a long time ago that i cannot find or remember so i need help
i have employee schedules that i need to how many minutes they work in different windows
as you will see in the example schedules i have schedule formats that very widely so cant use a simple left/right and their start/end times vary widely.
if the start or end in the middle of a window i only want to know the amount of minutes that they are on the schedule during that window.
i know to find the techs start and end time since the format changes i can use left for the start time since it is always on the left and search to find "am" for number of characters and for the end time the same idea with a mid but my formula was getting way to long and complicated and i only had the first window done which was the easiest. there has to be an easier way
the windows are:
8am-12pm
12pm-5pm
5pm-7pm
example schedules are (and the formatting varries):
<colgroup><col width="250"></colgroup><tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
to make it easier for me, the schedule is in column Q and the minutes per timeframe will be in columns X/Y/Z (in order of time)
hopefully this is enough without attatching the actual file since i cant do that, thanks in advance for the help.
i have employee schedules that i need to how many minutes they work in different windows
as you will see in the example schedules i have schedule formats that very widely so cant use a simple left/right and their start/end times vary widely.
if the start or end in the middle of a window i only want to know the amount of minutes that they are on the schedule during that window.
i know to find the techs start and end time since the format changes i can use left for the start time since it is always on the left and search to find "am" for number of characters and for the end time the same idea with a mid but my formula was getting way to long and complicated and i only had the first window done which was the easiest. there has to be an easier way
the windows are:
8am-12pm
12pm-5pm
5pm-7pm
example schedules are (and the formatting varries):
08 AM - 04 PM |
<colgroup><col width="250"></colgroup><tbody>
</tbody>
07:15 AM - 05:00 PM |
<tbody>
</tbody>
<tbody> </tbody>
<tbody> </tbody>
<tbody> </tbody> |
<tbody>
</tbody>
09 AM - 03 PM |
<tbody>
</tbody>
to make it easier for me, the schedule is in column Q and the minutes per timeframe will be in columns X/Y/Z (in order of time)
hopefully this is enough without attatching the actual file since i cant do that, thanks in advance for the help.