Data Validation to prevent time overlapping hours report

Hi everyone,

I am trying to figure out, how to setup data validation on such way, that if a time entry crosses into an already existing time lapse an error/warning is shown.

Because I am in Europe, I use European formats

Example:

Date	from	until
29.10.2021	13:00	14:30
30.10.2021	09:45	10:30

As we can see in the example on the 29th of October the starting time is 1pm until 2:30 pm while the second example is on 30th October from 9:45am until 10:30am.

If possible, I would like to have the mentioned data validation to prevent, that I record time, which has been already booked.

For example, if I would enter on 29th October to work from 14:00 until 15:00 (2am - 3pm), the system should warn me, because I have already an entry on the specific date from 1pm until 2:30pm and therefore it is overlapping.

I found an article but it is somehow not working on my side using the following formula:

Please Login or Register  to view this content.

Unfortunately, being a greenhorn and not often here, I am not allowed to add the at sign, which is in the code

Therefore I replaced the at sign and just wrote at, sorry for that!

Please Login or Register  to view this content.

When I try to use this formula, I can try to enter any starting time, I get the error window, whereas I understand, that 24hrs are somehow already occupied (no clue what I am doing wrong). I also tried to convert the date from European into US and combine date with time but it didn't like that either.

Maybe there is a simpler way to solve the issue and I do not need to use the function with indirect and could use something else?

It wouldn't be the end of the world, if there is no solution for it but it would be a "nice to have".

Thank you very much for any help or suggestions,

Mike

OK,
let me try

Hopefully the attachment is good enough

Column A is the date
Column B starting time
Column C ending time
Column D which Customer (not needed regarding my question, will be used for reporting)
Column E Calculation how much time was used and also an experiment

Thanx a million for the help

Good morning,

Thank you very much for helping out. Ups, sorry that was my mistake regarding the formatting!

I have downloaded your updated version but I am doing something wrong, because filling out the next row, nothing is happening.
While filling out row 7, there was no warning, that I entered time period, which is overlapping with the time period
of row 6.
(See attachment)

When I try to look at your result and I go onto Data and from there into Data Validation, it is empty, I do not see any formula. Am I looking at the wrong place or did something go wrong here?

I see, that your file is slightly bigger than mine but I do not understand, where I have to look or what I need to do, to make it run?
What I am doing wrong?

Thank you so much for the help,

Mike

Hello again,

sorry for my late reply but had several duties yesterday and this morning and therefore was not able to reply.

The formula is working and I would like to thank you for that. Need to study it to understand what it is doing but it is working and that is the most important.

Have a fantastic week,

Thanx again,

Mike

Done, thank you... you were 1 minute ahead of me