Need to create a "shift" column from a time column

Mommaduck

I have a column with the time (hh:mm) an interaction took place. I have another (currently blank) column for shift that I need to populate from the time.

The "shift" during which an interaction took place would be: 7Am-12:59PM as Day, 1-8:59PM as afternoon and 9PM-6:59AM as night.

I'm not sure where to start. How do I construct a formula that basically tells me If time is between 7:00AM and 12:59 PM mark this column Day? I'm guessing it's an IF statement but I don't know how to write the condition for between the 2 times and then populate the answer I need. This is what I tried but it came back Imparseable.

=IF([Combined time arrived]1,>7:00AM,<12:59PM,"Day"

Was also thinking maybe it should maybe be a Nested IF formula but when I tried to enter the Combined Time arrived the second time it bounced me out.

Any advice would be wonderful!!! Thank you!!

Paul Newcome

Ah. Now you are delving into the "wonderful" world of time in Smartsheet. Smartsheet is currently unable to calculate time. As such, the first step is to convert the text string into a numerical value. THEN we can make a comparison and output your desired result.

How is 12:## AM displayed? Is it 12:##AM or is it 00:##AM?

Mommaduck

I knew it wasn't the best for that as I've talked to support and they've told me -"not yet but things are always changing" - so I call them every 4-6 months to check! Some things have improved!! But I'm also getting bolder in my drive to make all columns work without exporting to Excel

This is a totally different sheet than my previous project. This is an intake record sheet for a clinic. Time is entered in the normal method-not military, so 12:45AM. There actually are separate columns for hour, minutes and AM/PM and then one column where they are joined. The individual columns are generated by a form staff completes. Joined column uses a formula. I don't care if the shift "result" comes back as a word or as a colored radio button-ish answer. Just something I can count to report on.

There's also a need for total time spent that would need to sometimes count time over days. I did one prodesk call for that and they had to get back to me by email after they combed the community. They did find formulas for me but they don't make sense to me Have another ProDesk scheduled for next week but feel free to comment!

Time area of sheet looks like this

Below is the response they sent me. I think you were actually one of the community members referenced! I'm just used to basic count type formulas and = signs. These just don't start with anything I've seen before. Similar to time, date is generated by form, combined date and time created by formula

As always, many thanks for your insight!!

I was able to do some research and testing and believe I have found a solution for you to calculate the difference in Times that may expand over 24 hours and you won't have to use military time.

I located a post on our Community site with a published Sheet to demonstrate this and have tested the formulas in my Sheet to confirm that it works great.

You can use your Start and End Date columns and Start and End Time columns. You will need to create a SUM column and use this formula: ((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)

And in the 'Difference' Column us this formula: INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60

A published Sheet version posted on Community can be found here and the Community post here: https://community.smartsheet.com/discussion/comment/240304#Comment_240304

Paul Newcome

Haha. Yep. That's one of mine. I actually have a few different time related solutions floating around out there. The solution in these links though would require some tweaking to be able to account for AM and PM, but I do believe I have something for that already.

The fact that you already have separate columns for the hours, minutes, and AM/PM actually helps a lot because that is one of the first things that gets done. It just makes everything else a lot easier.

Let me dig through my notes and see if I can adapt something to your specific scenario. Hang in there... Haha

Mommaduck

You're the best!!

Paul Newcome

@Mommaduck Quick question... What if the interaction overlaps shift? For example you have an Arrived Time of 12:00 PM which would be "Day", but a Departure Time of 2:00PM which would be "Afternoon"?

Mommaduck

Inquiring minds...No one has brought up that reality :(. Historically, we've just gone on arrival time so I guess we'll keep to that! This is the formula that's been being applied when we export to Excel. We had been using Excel for combined time because SS couldn't do it before (I just finally got it in SS this week).... . But it seems combined date/time in Excel uses military time....ugh! Can we have a lot of conditions? IF {TIME HOUR} is AND {TIME AM/PM} is...

Paul Newcome

@Mommaduck I have your duration solution put together.

Once you are able to tell me the logic behind which shift you want displayed (multiple shifts can be displayed for overlap if you want), we should be able to wrap this up.

I didn't have any solution "pre-built" already that would be easy to adapt to your particular scenario, so I went ahead and threw one together from scratch for you. I will adjust the column names to match yours and make the formulas easier to access once we finish it off and then provide a published link for you.

Paul Newcome

Give THIS a gander. There are two "helper" columns to convert the times into working numbers then there is a Shift column and a Duration column.

Mommaduck

I think we need to suggest the creation of a Jeopardy show for SS formulas - you would absolutely be the champ!! They all brought back a number except for total duration. I think I had it entered as you did but it came back Unparseable.

So I re-did the formula by clicking on the appropriate cells and keeping the extra [].

=INT([Finish Time]@row - [Start Time]@row) + ":" + (([Finish Time]@row) - INT([Finish Time]@row - [Start Time]@row)) * 60

That seems to work. So here's the really dumb question, that duration number is showing hours:minutes, right?

Which brings me to the column I had hidden, Check. Currently, I manually go in and mark that column for any lines that, after export and applied formulas in Excel, shows a negative number or a number greater than 24 hours. I had thought once I got duration in SS I could write an automation for that so duration would appear highlighted in those instances (and the people entering the data could then go back and fix errors!). That didn't work so I tried using your shift formula in the check column - but clearly I'm missing something! I keep trying to learn from you but your miles ahead!! Idea?? Initially did 0 not 0:00 and just 8 not 8:00. Used the 8 over 24 so I could see if it was working before dragging the formula down 3000 rows!!

I'm calling it quits for the weekend now and doing what I do much better - craft greeting cards and cook! Thank you for all you have done!! Have a wonderful weekend and stay safe!

Paul Newcome

Enjoy your weekend. I have about another hour left before I get to go play in the kitchen. Haha.

I am going to go ahead and answer now though while my brain is still in Smartsheet mode (or you may fall victim to the Monday Morning Mush that is my brain if I wait), but you feel free to wait until your weekend is over. I just want to get this out here while I am still able to brain a little bit. Haha

The reason the Duration formula did not work initially is because I did not have spaces in my column names. "StartTime" vs "[Start Time]". It is easier for me personally to remove the spaces so I don't accidentally forget a square bracket. Haha.

You are correct that the duration column is reading as hh:mm. That is for display only. That is actually a text string and cannot be used as a number.

To have it as a number for comparison, we would need a different (and simpler) formula.

Additionally... I made an assumption that the interactions would cover no more than two days. Start on day 1 and end on day 2 kind of thing. I have updated it accordingly. The formula that determined that is in the FinishTime column and has been updated on the Published sheet for you.

Finally... I changed the Duration column name in the sheet to [Display Duration], and added another column called [Calculating Duration]. This new column will generate a number that can be used for your calculations. Some examples of numbers you would see are

8.5 = 8:30 = 8 1/2 Hours = 8 Hours and 30 Minutes

9.25 = 9:15 = 9 1/4 Hours = 9 Hours and 15 Minutes

You can use this new column as a numerical value to compare to more than 24 hours or add them up to get a total amount of time spent, etc.

So now you can use the [Calculating Duration] column in an IF statement in your [Check these] column like so...

=IF([Calculating Duration]@row < 0, "Negative", IF([Calculating Duration]@row > 8, "High"))

Mommaduck

Made a really foolish mistake!! When I went to pull the formula down the column using highlight first cell, SHIFT on last cell, Ctrl D - I put in Ctrl V. Lost the formula and SS wasn't letting me undo :( . Came back to this trail to your "give this a gander"

and re-entered your formula - I thought! But it's coming back Incorrect argument

For the life of me I can not find the difference between your formula and what I entered ! I know the formula worked before. Please-do you see what I did wrong?? As I typed in the formula, any time it allowed me to chose a function from a drop down (like IF) i used that.

Thank you!!!

Paul Newcome

Take one of the closing parenthesis from the very end and put it after the first am/pm column reference. That first CONTAINS function didn't get closed out, so basically you are telling the formula to take everything after that and search for the "P".

One thing you can do if this happens again (I have used this method more than once) is to look at your activity log (not the cell history). It will actually show the formula and it's output. You can copy/paste the previous formula that was working straight from the activity log.

You should also be able to copy/paste directly from the Published Sheet as well instead of having to manually enter.

Mommaduck

Activity log - who knew!!! That was amazing!! and just taught me LOADS!!! for so many sheets!! I had tried cell history with no luck. Didn't know this other log existed!!

Which means I'm finally back to finishing Time spent and identifying times that maybe "suspect".

I plugged in your formula for the Check These (Had to use a new column "Need check" as I had already manually entered red lights in Check these that I couldn't take out yet). But I realized I have nothing in the Calculating Duration column for it to calculate! I went back to your notes - but how do I get that "number" from display duration? Is it actually the number I have in Finish Time Column? If yes, would there be value to me reducing how many digits display after the decimal?

THANK YOU!!!!

Paul Newcome

Haha. I found that out when I got into a sheet and found all instances of a very complex formula erased. There was a fair amount of panic involved. I checked the activity log to find out "who dun it" and found the original formula tucked away. Phew!

It does appear as if there may be an issue with your Display Duration though.

Your Calculating Duration is simply

=[Finish Time]@row - [Start Time]@row

Your Display duration should reflect this number as hh:mm, but it doesn't seem to be. For example, row 1 should have a display duration of 0:15 because it was only 15 minutes. You may want to double check that formula in comparison to the published sheet and make sure that one copied over correctly as well. You shouldn't have to adjust any number of digits shown.