Flagging a Date and Time Overlap

Paul Newcome · 11/03/20

@Megha布It looks like you have an extra closing parenthesis after your first range in the COUNTIFS.

=IF(COUNTIFS(Site:Site)..........

Remove that and see if it works for you.

Muni Pavan Kumar Reddy Kona · 02/16/21

I read this blog. It is very useful.

I do not want time overlap. For me only date overlap of particular column is important.

I am confused about the formulas. I have only 2 columns "task start date", "task end date". I want to trigger an overlap when 2 are more dates same on "task end date" colomn. Can you please help

Paul Newcome · 02/18/21

@Muni Pavan Kumar Reddy KonaAre you only wanting to compare the end date column, or are you wanting to also incorporate the start date column?

Muni Pavan Kumar Reddy Kona · 02/23/21

@Paul Newcome, I want only end date compared in all cells, if two or more end dates are equal we need to flag

Paul Newcome · 02/23/21

@Muni Pavan Kumar Reddy KonaIf you just want to flag any rows with equal end dates, it would look something like this...

=IF(COUNFITS([End Date]:[End Date], [End Date]@row) > 1, 1)

Muni Pavan Kumar Reddy Kona · 02/23/21

@Paul Newcome,

It did not work, Please find the attached screen shot.

Error.JPG

Paul Newcome · 02/23/21

@Muni Pavan Kumar Reddy KonaWhat exactly do you mean by it doesn't work? Is it not flagging duplicate dates or are you getting an error code?

Muni Pavan Kumar Reddy Kona · 02/24/21

@Paul Newcome, My apologies. It it working. Actual formula you gave is below.

=IF(COUNFITS([End Date]:[End Date], [End Date]@row) > 1, 1

But small typo =IF(COUNTIFS([End Date]:[End Date], [End Date]@row) > 1, 1)

I placed them as flag it perfectly work fine for me. Thanks for your help.

Paul Newcome · 02/24/21

@Muni Pavan Kumar Reddy KonaSorry about that. Sometimes my fingers move a little faster than they should.

TsmithFastSigns · 02/11/22

@Paul NewcomeI saw a sheet you made for someone else creating a flag for date and time overlap. Can you share that again?

Paul Newcome · 02/11/22

@TsmithFastSignsTake a look at the below thread. There should be what you need in there somewhere. I think it is up to 9 pages now. Hahaha

I have decided to create a post that consolidates all of the different time based solutions that I have put together.<\/p>

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.<\/p>

I thought it would be helpful to have them all in one place instead of spread out.<\/p>

I welcome all feedback, suggestions, and additions.<\/p>

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.<\/p>

I will also try to post various tips and tricks as I think of them as well such as:<\/p>

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:<\/p>

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))<\/p>

Calculating Time Worked for Employees<\/a><\/p>

Can you calculate time in Smartsheet?<\/a><\/p>

Conversion of timezones (Solved Formula Included)<\/a><\/p>

Date\/Time\/Year<\/a><\/p>

Flagging a Date and Time Overlap<\/a><\/p>

How do I create time of day columns?<\/a><\/p>

Need to create a "shift" column from a time column<\/a><\/p>","bodyRaw":"[{\"insert\":\"I have decided to create a post that consolidates all of the different time based solutions that I have put together.\\nSome of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.\\n\\nI thought it would be helpful to have them all in one place instead of spread out.\\n\\nI welcome all feedback, suggestions, and additions.\\n\\nI'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.\\n\\nI will also try to post various tips and tricks as I think of them as well such as:\\nIt is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:\\n=VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) + IF(CONTAINS(\\\"p\\\", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) = 12, -12))\\n\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662\"},\"insert\":\"Calculating Time Worked for Employees\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d861f6ccf0e048b98906495ed74671fc\"},\"insert\":\"Can you calculate time in Smartsheet?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=0122690b7ff744c68653e4dd0fa3cd81\"},\"insert\":\"Conversion of timezones (Solved Formula Included)\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=8b6ddddb66bb4b3db6fbbec745b46795\"},\"insert\":\"Date\\\/Time\\\/Year\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d39961b785de4312ba2c98b8d148b3bd\"},\"insert\":\"Flagging a Date and Time Overlap\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=e1b1d4c042e84f8b82574cbf378bd767\"},\"insert\":\"How do I create time of day columns?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=54f270e0ed1440f6a25de2453119655c\"},\"insert\":\"Need to create a \\\"shift\\\" column from a time column\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-06-08T15:55:08+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T20:54:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68947\/formulas-for-calculating-time","embedType":"quote","name":"Formulas for Calculating Time"}"> https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

TsmithFastSigns · 02/11/22

@Paul NewcomeYou are such an asset to this community. Good man!

TsmithFastSigns · 02/11/22

What am I doing wrong here,@Paul Newcome

=IF(COUNTIFS([VEHICLE #]:[VEHICLE #], [VEHICLE #]@row, DATE:DATE,[email protected], FINISH:FINISH, @cell >=[email protected], START:START, @cell <=[email protected]> 1, 1)

I changed your [virtual license] to [vehicle #]. I'm trying to make a vehicle schedule that will notify of overlap of vehicle use.

I'm getting "INCORRECT ARGUMENT SET" as an error.

Paul Newcome · 02/28/22

@TsmithFastSignsYou just forgot to close out the COUNTIFS.

=IF(COUNTIFS([VEHICLE #]:[VEHICLE #], [VEHICLE #]@row, DATE:DATE,[email protected], FINISH:FINISH, @cell >=[email protected], START:START, @cell <=[email protected]) > 1, 1)

Flagging a Date and Time Overlap

Comments

Help Article Resources

Categories