Identify Overlapping Dates and Times in Excel - EASY Formula

[@JacopoT]

Straight to the point and crystal clear. Thanks!

[@MyOnlineTrainingHub]

Thanks so much!

[@cwjr1968]

This is exactly what I needed. It will save me a ton of time, ensuring that my employees enter their clock times correctly. Thank you!😁

[@MyOnlineTrainingHub]

So pleased it was helpful 😊

[@de2veloso]

Simple and efficient. Perfectly adapted to what I needed. Thank you!!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@IvanCortinas_ES]

Curious tactic. Very interesting Mynda. Thank you very much.

[@MyOnlineTrainingHub]

Glad you enjoyed it, Ivan!

[@nikosstasinopoulos6246]

Great!just one question..is it possible to count how many days these ranges have in common but without a helper column, regardless starting date(it can be earlier than the previous etc) and ignoring blank cells?in this case would be 3 (c6,c7,f19)..Thnx!

[@MyOnlineTrainingHub]

Probably. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@chrism9037]

Very clever trick Mynda!

[@MyOnlineTrainingHub]

Cheers, Chris!

[@whatsup5717]

What about if the end time of one same as the start time of the other and I want it to consider as an overlap? As A meeting ends at 8:00 and B meeting starts at 8:00. Thanks

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@GianCarloFabros]

Hi Mynda, this process really helpful. What I am looking to do now is determine which cell is the overlap so it can easily be found instead of manually combing through a few hundred time periods (in my case). Thank you very much!

[@MyOnlineTrainingHub]

Glad it was helpful! Please post your question and sample Excel file on our forum where someone can help you with the next step: www.myonlinetraininghub.com/excel-forum

[@darrylmorgan]

Hi Mynda!Cool Tutorial,Really Helpful Explanations...Thank You :)

[@MyOnlineTrainingHub]

You are most welcome, Darryl!

[@mobywhite]

Is there a reason this wouldn't work in 365? Mine doesn't seem to understand the [table] written like that or with the @ signs?

[@MyOnlineTrainingHub]

It works in 365 for me. The @ symbol is the intersect operator and specifies the value on the current row of the named column. You must add another set of square brackets around it like so [@[Start Date]]. If you use your mouse to select the fields, Excel will automatically add the @ operator. Note, in this formula: =SUMPRODUCT(([@[Start Date]][Start Date]))>1 The first column reference is referencing the row for said column, and the second is referencing the whole column.

[@mobywhite]

@MyOnlineTrainingHub is there a way to do the same thing but in google sheets?

[@francescosuma7050]

Useful! Many thanks! 💪🏻

[@MyOnlineTrainingHub]

Great to hear 😊

[@diegorojas9737]

Does this only work if dates are previously sorted chronologically? I find it doesn't work when dates in table aren't sorted in that way and I'm wondering if there is any tweak that can be used for a table with multiple possible dates overlapping but they are disperse.

[@tiffanyadamsc]

Can you create a video using the cells references as well?

[@MyOnlineTrainingHub]

You can download the file and convert the table to a range (right-click the table) and the formulas will switch to cell references.

[@devilwearswhite]

This is absolutely amazing and thank you for breaking it down. I do have one question though, how would I be able to incorporate days of the week as a third criteria in addition to room and time?

[@MyOnlineTrainingHub]

Thank you! Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@mood-zn3sp]

thank you so much❤❤❤❤

[@MyOnlineTrainingHub]

You're welcome 😊

[@SundasKhalidSnowWhite]

Amazing 🤩 thank for this quick tip it is very helpful.

[@MyOnlineTrainingHub]

You’re most welcome 😊

[@UltimateGuruGaming]

suppose if activity G starts on day 17 than how can we calculate overlapping days between these 3 activities ?

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@mehulthanki2352]

I want to calculate the overlapping of the start date and the end date with financial years and based on that I want an answer in the cell which year has more days in it, kindly guide me.

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@RESERVERUANGAN]

there is no FILER in my Excel. Can I use another formula? thanks

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@אוריבןיעקב-י9מ]

Hi mynda, the download link is not working

[@MyOnlineTrainingHub]

Oops, sorry. It's working now.

[@ayelebayu1994]

Hi , sir there is some question and i feel difficult to solve so plz help me . using excel formula how to calculate overlap payment at list 500 employees ,the main question is no one paid with an identity date and time , if I insert the name it display you are paid or any color displayed

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@atiqkhan9637]

wow nice trick mind blowing

[@MyOnlineTrainingHub]

Thank you so much 😀

[@furqanmehboob6258]

Hi , mam there is some question and i feel difficult to solve so plz can i contact ?

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@partymaschine92]

Hey Mynda, I love your videos with handy tips and tricks! SUMPRODUCT was and still is one of the most powerful function. I am using this function for counting the amount of employees at a certain time of a day, like (shift begin date and time = time of certain day) Additional conditions will help to identify special qualification of an employee. FILTER is useful there, too. Depending on the conditions you can have a very detailed count of groups of employees

[@MyOnlineTrainingHub]

Thanks so much 🙏 I love SUMPRODUCT too. 😊

[@osielg9889]

GREATS! 👏🏻

[@MyOnlineTrainingHub]

Glad you like it!

[@osielg9889]

@@MyOnlineTrainingHub Es genial tu contendio, siempre disfruto mucho! Que estes muy bien :)

[@prajaypawar19]

Super formula

[@MyOnlineTrainingHub]

Great to hear it was helpful!

[@robertk4vl412]

This video is great but I am trying to do this with cells that have both date and time in them.

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@BrainyBrunetteBarbie]

A formula is ALWAYS better.

[@aniketparab7744]

Y this formula not work for me

[@MyOnlineTrainingHub]

Hard to say, Aniket. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@listerneiltianchon8886]

Hi Can someone help me how to set up a formula to identify if there is an overlap between 2 ranges of dates. Example 1st Range: 01/01/202 to 12/31/2022 2nd Range: 01/01/2022 to 12/31/2023 I would like to learn if; 1. The if there is an ovelap like True or false 2. the overlapping dates. Thank you so much in advance ❤️❤️

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@shadeburst]

Very cool. You rushed the logical tests a bit and my slow brain took a while to apply the logic to another application that I use daily. If only SUMPRODUCT() respected a slicer selection like SUBTOTAL() does...

[@MyOnlineTrainingHub]

Glad it was useful for you. It's not so much that SUBTOTAL respects the Slicer, rather it has the ability to ignore filtered rows. You can also try AGGREGATE for this: www.myonlinetraininghub.com/excel-aggregate-function

[@PEDERSTEENBERG-d5h]

mmmmmmmmmmmmmmmmmmmmmmmm

[@ozgur937]

Feels like good old days of sumproduct() magic.

[@MyOnlineTrainingHub]

Never gets old 😁