How do I create time of day columns?

Paul Newcome

@James Elliott Glad you were able to get it figured out.

mbsamuel6

@Paul Newcome

Hi,

I'm trying to implement the formulas you have posted here https://app.smartsheet.com/b/publish?EQBCT=e1b1d4c042e84f8b82574cbf378bd767, but I'm getting a few errors.

For some reason, the formulas are not reading when it comes to "Duration, Years, Months, and Days".

As seen below in the screenshot its saying "invalid data type". Any reason why? Is it my columns? I have them all set to "Text".

All I did was copy and paste what you had in the link above and placed it in my smartsheet.

Thank you!

mbsamuel6

@Paul Newcome

Hi,

I have a similar issue and for some reason its not working on my end.

All I did was copy from what you have posted here: https://app.smartsheet.com/b/publish?EQBCT=e1b1d4c042e84f8b82574cbf378bd767

and pasted it my SMARTSHEET. When I do that I am getting "invalid operation" errors as seen below. Any idea why?

Paul Newcome

@mbsamuel6 Double check that your date columns are in fact set as date type columns.

mbsamuel6

Face palm. That did it. Sorry for the double post. I tried to delete it but it wouldn't let me.

Thank you. :)

Paul Newcome

No worries and happy to help. 👍️

The way I worked this one was like this...

I started at the end of the solution. Not working. Error.

So I look at the formula to see what cell(s) it is referencing. Do any of those have an error? Yup. The one right before it in the chain.

We take a look at that one. None of the cells it is referencing have an error, so we know the originating error is coming from somewhere in this particular formula.

So we look at each of the sections of this formula to try to figure out exactly where the problem may be.

We know that both the End and Start Time Calc formulas are outputting numbers, so we know that isn't the issue.

The only other place that could cause an issue in this particular formula would be where we subtract the date from the date. We know that if they are both dates then it SHOULD be working, so we start with the simplest check of... Are they in Date type columns?

If they were date type columns, the next step would be some testing to find out exactly how those dates are being stored on the back-end, but we didn't need to go that far because the column type was the issue and is now fixed allowing everything to work properly.

Short version... Start at the end of the problem. Trace it back to the beginning of the problem. Start troubleshooting with the easiest things to fix.

Katie Lafferty

Hello Paul, I read through the entire post on this issue. I copy/pasted all your formulas and the date columns are a date type. I get BLOCKED & UNPARSEABLE. if you have any ideas it would be greatly appreciated.

Katie Lafferty

Please disregard...your formulas worked perfectly :) Now I just have to figure out how to modify your formulas to show Total Hours only (not days).