Formula irregularity

Turks · 05/10/19

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

Andrée Starå · 05/10/19

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 · 05/10/19

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 · 05/11/19

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 · 05/11/19

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å · 05/11/19

Ok.

Best,

Andrée

Turks · 05/12/19

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å · 05/13/19

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 · 05/13/19

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

Paul Newcome · 05/13/19

Glad you were able to get it working.

Formula irregularity

Comments

Help Article Resources

Categories