Formula irregularity

Turks

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

 

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.