Sort by start and end times

Patti Steele

I know this is something that Smartsheet has been working on a for a while. Whenever I ask about it, I'm told that it's on the list.

Having a way to sort rows by date, and time, seems like a VERY basic tool.  I'm getting to the point where I can't recommend Smartsheet to other planners to use because it's crucial to sort thing by date and time.  Even if I make a drop down with 15 minute increments, I still can't sort by it.  

Can I get an update on when this will be added, or it's been added and I just don't see it? It's more than a year since I was told it was on the list of updates.  I don't want to go back to using excel!

Thanks for any information anyone can provide.

Patti

Adam Overton

I don't have an answer to your question, but I'd like to propose a work around. Make a new column called "Sort me" or whatever you want. In it put a formula like this in the first column and fill down:

=(Start1 - MIN(Start:Start) + 1) * (MAX(End:End) - MIN(End:End)) + End1 - MIN(End:End)

You should be able to sort by this column and get the result you want. You can then hide this column if you like.

Nerdy math explanation for the interested:

The first part (Start1 - Min(start:start) + 1) counts the number of days of this row's start date since the earliest start date and adds 1.

The second part (MAX(End:End) - MIN(End:End)) is longest number of days counted from the earliest end date to the latest.

I multiply these together so the number is big enough that even adding the number of days from the end date part is less significant than the first start date part.

The final part is the End1 - Min(End:End) which counts the days from between the earliest end date and the current row end date. Because the biggest version of this number is smaller than the smallest start date number, it just differentiates between identical start dates, but can't move one start date past another.

 

 

Patti Steele

Um, you're amazing.  I'm all about the nerdy math.  Thank you!

If I need to show the time range, what will that look like?  See my attachment.  I basically use this as a schedule of events.

Patti

Adam Overton

You could follow the same principle for Day vs Time. Convert the time to a number, like the minute of the day = (hour*60+minute), so 9:45am = 9*60+45, and 9:45pm = 21*60+45

Since there are 24*60 = 1440 minutes in a day, you need to multiply your Day number *1440

(Day1-Min(Day:Day) + 1) * 1440

then add your minutes to that.

Another way of thinking of it is "How many minutes is this start from 12am of the first day's event"?

Unfortunately there are no time functions in Smartsheet, so you have to parse them out as text. I think it is this:

=((Day1 - MIN(Day:Day) + 1) * 1440) + (VALUE(LEFT(Time1, FIND(":", Time1) - 1)) + (COUNTIF(Time1, FIND("pm", @cell) <> 0) * 12) * 60) + VALUE(MID(Time1, FIND(":", Time1) + 1, 2))

Ugly! But it should work. You might need to change "pm" to "PM" based on your picture. And Time1 should be Start1.

 

Adam Overton

I looked at my Date + time formula again because somebody asked about sorting by time, and it looks like I had an error. I think this is the corrected one:

=((Day1 - MIN(Day:Day) + 1) * 1440) + ((VALUE(LEFT(Time1, FIND(":", Time1) - 1)) + (COUNTIF(Time1, FIND("pm", @cell) <> 0) * 12)) * 60) + VALUE(MID(Time1, FIND(":", Time1) + 1, 2))

Basically, some missing parentheses meant the hour wasn't being multiplied by 60 properly to calculate what minute of the day.

 

Allison Hopkins

Hi Adam -You made my day with this formula! Thank you! I'm having issues with the hour of 12:00 PM sorting correctly. All other times sort great - but 12:00 PM (and other 12:?? times) always end up at the end. Any suggestions? Thanks for any help you can provide!

Adam Overton

Yes, I just looked at it. The problem is the that "12" is the lowest number in time hours, which requires a modification:

=((Day1 - MIN(Day:Day) + 1) * 1440) + ((IF(VALUE(LEFT(Time1, FIND(":", Time1) - 1)) = 12, 0, (VALUE(LEFT(Time1, FIND(":", Time1) - 1)))) + (COUNTIF(Time1, FIND("pm", @cell) <> 0) * 12)) * 60) + VALUE(MID(Time1, FIND(":", Time1) + 1, 2))

 

Basically there's now an IF in the middle that says if the hour is 12, pretend it is zero so that both 12 am and 12 pm will come before 1 am and 1 respectively.

IF(VALUE(LEFT(Time7, FIND(":", Time7) - 1)) = 12, 0, (VALUE(LEFT(Time7, FIND(":", Time7) - 1))))

 

 

Allison Hopkins

Thanks, Adam! I feel like I'm so close, but I must be doing something wrong because the 12 o'clock times still aren't sorting correctly.. Here is a snapshot of my sheet:

 

Here is the formula I'm using.

=((Date75 - MIN(Date:Date) + 1) * 1440) + IF(VALUE(LEFT(Start75, FIND(":", Start75) - 1)) = 12, 0, (VALUE(LEFT(Start75, FIND(":", Start75) - 1)))) + ((VALUE(LEFT(Start75, FIND(":", Start75) - 1)) + (COUNTIF(Start75, FIND("PM", @cell) <> 0) * 12)) * 60) + VALUE(MID(Start75, FIND(":", Start75) + 1, 2))

Any suggestions/help would be greatly appreciated! Thank you!

Adam Overton

There's definitely a chance this might work :

=((Date75 - MIN(Date:Date) + 1) * 1440) + IF(VALUE(LEFT(Start75, FIND(":", Start75) - 1)) = 12, 0, (VALUE(LEFT(Start75, FIND(":", Start75) - 1)))) + (COUNTIF(Start75, FIND("PM", @cell) <> 0) * 12)) * 60) + VALUE(MID(Start75, FIND(":", Start75) + 1, 2))

 

 

Allison Hopkins

Hi Adam - thanks for all of your help! I copied and pasted the text your provided and I get "#unparseable". I'm so bummed! I really want to be able to sort by date/time and I feel like I'm so, so close! Any other suggestions? Thanks!

Adam Overton

Unfortunately I'm not going to be able to put more time into this soon. I made my recommendation by putting my example and yours one above the other with a fixed font like courier and then comparing them/fixing the spacing. You have an extra  VALUE(LEFT sequence in yours so I deleted that but obviously I can't test it in your sheet.

The way I usually build these is to break it down into separate columns, and then cut and paste them all together. So if you look at my post from May 8, 2017 1:19 pm, I explain each step.

Number of days since my earliest date column: 

((Day1 - MIN(Day:Day) + 1) * 1440)

The "hours" part of the time:

LEFT(Start75, FIND(":", Start75) - 1)

 

The "hours" part converted to a number:

VALUE(LEFT(Start75, FIND(":", Start75) - 1))

 

The "hours" converted to a number, but twelve converted to zero:

IF(VALUE(LEFT(Start75, FIND(":", Start75) - 1)) = 12, 0, (VALUE(LEFT(Start75, FIND(":", Start75) - 1))))

 

A value that adds the minutes since 12am if there is a PM:

(COUNTIF(Start75, FIND("PM", @cell) <> 0) * 12)) * 60) 

 

The minutes part: 

VALUE(MID(Start75, FIND(":", Start75) + 1, 2))

 

That's how you could try and work it out yourself.

Alternatively, you might be able to rename your columns to the same as mine, paste in my function, and then rename them back and have that work!

 

 

Allison Hopkins

Hi Adam - thanks so much for all of your help. You have been so kind and gracious to help me - I truly appreciate it. I'm sorry for all of the follow up questions. Thank you very much!