Formula irregularity

Fairly new to Smartsheet, although I know my way around an Excel spreadsheet.

I am trying to create a count of how many reports are submitted per hour on each given day. The reports come in via a Web Form, into the Taxi Rank Data sheet.

I have created the following Sheet (see attached jpg)

with the following formula,

=COUNTIFS({Taxi Rank Data Range 2}, Hours8, {Taxi Rank Data Range 1}, $[Shift Date]$2)

My issue is that the formula works perfect for the first 5 timeslots but the remaining do not work, the timeslots are selected via a dropdown box and are exactly the same in both sheets. I have selected the entire Column during the Data range selection for the Hours (2) and Date(1). Not sure what I am doing wrong.

Any help would be appreciated.

Regards

Keith

Smartsheet Question.jpg

Find more posts tagged with

Formulas

Reports

Services

Comments

Andrée Starå

Hi Keith,

Are you getting any error message?

Can you describe your process in more detail and maybe share the sheet(s) or more screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

It seems to have something to do with the hours. It looks like the formula breaks once you get past 2400 and start your times with a leading zero.

I wonder... If you were to continue on down the line, does the formula start working again when you get to 1000?

Turks

Hi Paul,

I tested it at 1000 and it works again, I agree the formula appears to breakdown with the leading zero.

Does smartsheet handle the leading zero in times/numbers

I am going to alter the time format to include a colon and see if the formula works then.

Thanks Again

Turks

Hi Andre,

I think it is something to do with the leading zero, I will try and adjust the time format and if that does not work I will share the sheets with you.

Thanks

Keith

Andrée Starå

Ok.

Best,

Andrée

Turks

Worked OK after altering the format to include the colon, 01:00

I think it must relate to the way smartsheet recognises numbers with a leading zero.

All good thanks for your assistance.

Keith

Andrée Starå

Excellent!

I tried a little test of how Smartsheet recognizes the values, and this is what I found:

0100 = Text

1000 = Number

01:00 = Text

Hope that helps!

Best,

Andrée

Paul Newcome

This is what I was thinking. The leading zero was converting it to text instead of a number.

Paul Newcome

Glad you were able to get it working.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?