A formula for certain time intervals

[hsalim]

Hi All,

New user here and first post. I have a decent understanding of excel but was wondering if someone with a bit more knowledge can help on a small issue im having.

We get a monthly report in excel which shows who has been in and out of our building and this has time stamps on each user who enters/exits. What i would like to know is, is there a formula which will calculate the time a user comes in and allocate an interval time to it?

For e.g. We have 2 columns from this excel sheet, one is called "Time", the next is called "Interval". If we have 2 users, User A comes in at 09:32:45 - the interval I would like this to be; 09:30-18:00 (09:30 being the start time and 18:00 being the finishing time of the company), User B comes in at 09:05:26 - the interval for this would be 09:00-09:30.

Is it possible to have excel detect the times from the "Time" column and assign the appropriate interval to this? Would be great as i am currently having to do this manually.

Thanks,
Has

[Ash_Maverick]

Can you share the file with all the intervals

[hsalim]

Hi,

Please see attached spreadsheet.

I have removed information from the other columns as they include sensitive data but if you see what i mean by the "Time" & "Interval" columns, it would be great if there was a formula or something i could put into the "Interval" column instead of manually inserting the intervals.

Thanks,
Has

[oeldere]

Where do we see in your sheet Saccess which user is entered?

I think VLookup can solve the problem.

[hsalim]

Where do we see in your sheet Saccess which user is entered?

I think VLookup can solve the problem.

Like i mentioned, i have removed the first and last name of the users as this is sensistive data but the users column would usually have the first and last name of someone inside it.

[oeldere]

Then I have a questions to you:

1) How can we solve the problem if we can't see who is entered?
I supposed it was based on the name, but probably that is a false thought.

2) What are the criteria to show the time in the result column?

[Ash_Maverick]

please define the intervals like in this image

[hsalim]

please define the intervals like in this image

This is very similar to how mine is setup but please see the intervals I have below. Have also attached an updated excel sheet with some dummy information to make it less confusing.

Begin Time	End Time	Interval
08:00AM	09:00AM	08:00-09:00
09:00AM	09:30AM	09:00-09:30
09:30AM	18:00PM	09:30-18:00
18:00PM	19:00PM	18:00-19:00
19:00PM	08:00AM	19:00-08:00

[TudyBTH]

Hi,
Try this

=TEXT(INDEX($K$2:$K$6,IFERROR(MATCH(B2,$K$2:$K$6,1),5)),"HH:MM")&"-"&TEXT(INDEX($L$2:$L$6,IFERROR(MATCH(B2,$K$2:$K$6,1),5)),"HH:MM")

It has created a list of the time intervals (K1:L6)
Capture1.JPG
see attached

[hsalim]

Hi,
Try this

=TEXT(INDEX($K$2:$K$6,IFERROR(MATCH(B2,$K$2:$K$6,1),5)),"HH:MM")&"-"&TEXT(INDEX($L$2:$L$6,IFERROR(MATCH(B2,$K$2:$K$6,1),5)),"HH:MM")

It has created a list of the time intervals (K1:L6)
Attachment 477256
see attached

Hi Tudy,

That works a charm.

Many thanks for providing the formula. Much appreciated.

Thanks,
Has