Index match with time variables

[chriswrcg]

I need to pull data out of a data set for an individuals work day. To do this I am using an index match formula but I am having issues with the result. The data for the individual is one work day but since they start work at 1:30 in the afternoon and does not finish some days until after midnight the formula can not pull the correct data since it is splitting time into two work days. How can I adjust my formula so that it pulls the entire work day? Please see the example.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, with more detail, in your title.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[torachan]

in O2 copy down

=IF(D1>D2,D1+1,D2)-D1

[chriswrcg]

in O2 copy down

I don't think this is solving my problem. It's just another way to do the math if I am not mistaken. I need to be able to tell the system I only want to see the results between two time variables that happen to be from separate days. "If Name equals this and date equals this and starting time is greater than this and ending time is less than this then return this value" I got the name and the date part I just need to add the greater than "this" time variable and less than "this" time variable part of it.

[AliGW]

What are the start and end times you wish to use?

[chriswrcg]

What are the start and end times you wish to use?

If you look at the example, Day one starts with time stamp 15:27:11 on 7/21/2021 and ends at 00:18:05 on 07/22/2021.
Day two starts at 15:38:05 07/22/2021 and ends on 00:19:19 07/23/2021.
Even though the "work day" is two days per the calendar I need the system to see it as one day. If possible

[AliGW]

That doesn't answer my question.

What is the EARLIEST time someone can start a working day? Is it 13:30?

[chriswrcg]

Yes on most days. for second shift. I'm sorry this is confusing.

[AliGW]

See if this gets you any closer:

=IFERROR(@INDEX(Data!$O$2:$O$1034921,AGGREGATE(15,6,ROW($1:$1032996)/((Data!$A$2:$A$1034921=$A$1)*(Data!$L$2:$L$1034921+Data!$D$2:$D$1034921>=C$2)*(Data!$L$2:$L$1034921+Data!$D$2:$D$1034921<C$2+1.5625)),@ROW(1:1))),"")

[chriswrcg]

I think that is working. I am getting some weird gap times but that is the system trying to calculate between 23:58:10 and 00:04:14. I would get a more accurate result if I convert the latter to 24:04:14 but I can do that with a search and replace. Thanks for all the help.

[torachan]

@chriswrcg, your original formula was merely showing the time difference between the preceeding time in the same column (=D2-D1)
This was falling over on the cases that went over midnight - all my formula did was cater for this case - did you even try - you would then see it works.

[AliGW]

No worries. You may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Croweater]

Try in O2; Drag down.

Please Login or Register  to view this content.