Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Here's a fun one...

Options
Preston
Preston ✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

Since we can't calculate time...(yet)<-----hint, hint Cool

 

I'm trying to write an IF formula to determine hours. I have a Shift Start (Dropdown List) column, a Shift End (Dropdown List) column, and a Total Hours (Text/Number) column. The dropdown values are listed below. Is this doable? Here's what I'm starting with:

 

=IF(AND([Shift Start]1 = "4:00AM", [Shift End]1 = "4:15AM"), 0.25, 0)

 

4:00AM

4:15AM

4:30AM

4:45AM

5:00AM

5:15AM

5:30AM

5:45AM

6:00AM

6:15AM

6:30AM

6:45AM

7:00AM

7:15AM

7:30AM

7:45AM

8:00AM

8:15AM

8:30AM

8:45AM

9:00AM

9:15AM

9:30AM

9:45AM

10:00AM

10:15AM

10:30AM

10:45AM

11:00AM

11:15AM

11:30AM

11:45AM

12:00PM

12:15PM

12:30PM

12:45PM

1:00PM

1:15PM

1:30PM

1:45PM

2:00PM

2:15PM

2:30PM

2:45PM

3:00PM

3:15PM

3:30PM

3:45PM

4:00PM

4:15PM

4:30PM

4:45PM

5:00PM

5:15PM

5:30PM

5:45PM

6:00PM

6:15PM

6:30PM

6:45PM

7:00PM

7:15PM

7:30PM

7:45PM

8:00PM

8:15PM

8:30PM

8:45PM

9:00PM

9:15PM

9:30PM

9:45PM

10:00PM

10:15PM

10:30PM

10:45PM

11:00PM

11:15PM

11:30PM

11:45PM

12:00AM 

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Preston,

     

    I'm trying to get my head around this.

    Can the user select "6:00AM" and "4:00AM" for start and end? Is that 22 hours or invalid?

    Are you planning to use a WebForm, is that why you want such a long drop down list?

     

    Would your users understand the 24hour clock (it would make the formula smaller)?

     

    Craig

  • Preston
    Preston ✭✭✭✭✭
    Options

    Hi Craig,

     

    Thanks for the reply! 

    If the user did select those it could give an error (that would be acceptable). This would signify to an admin there's invalid input. 

    I'm actually using Appsheet, the enum lists are much easier for users.

    Unfortunately, the 24 hour clock would give my users trouble...Frown

     

    Preston

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Preston,

     

    So the spec is:

    1. User input shift start and shift end

    2. Shift end must be > shift start or error is generated for the admin

    3. Shift hours are calculated by shift-end minus shift-start. 

    - shifts can be up to 16 hoursy(4am to midnight)

    4. User inputs from Webform

     

    It might be very easy if the drop-down list was

     

    00-4:00AM

    01-4:15AM

    02-4:30AM

    03-4:45AM

     

    etc...

     

    or 

    4:00AM-00

    4:15AM-01

    4:30AM-02

    4:45AM-03

    etc...

     

    I can build you a prototype later tonight (just the sheet, not the Webform)

     

    Craig

     

  • Preston
    Preston ✭✭✭✭✭
    edited 01/11/16
    Options

    Now you're talking...I could change the dropdown list to the latter layout. That sounds like a plan! I appreciate your help!

     

    Now, watch time tracking be rolled out as a feature enhancement next week! Ha! 

  • Travis
    Travis Employee
    Options

    Preston, check the following post from Kara. She posted a few time formulas which can calculate the difference between two given times.

     

    https://community.smartsheet.com/discussion/need-column-property-type-clock-or-time-day?page=1#comment-374

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Preston,

     

    I'll have to digest Travis' (Kara's) link. My thinking was it would not be so complicated so I need to see if I missed something.

    I have unexpected but welcome dinner invitation so I'll look at the formula in the morning.

     

    Craig

  • Preston
    Preston ✭✭✭✭✭
    Options

    I'll second the digesting...and I didn't even get invited to dinner! Laughing

    Seriously, thanks for entertaining my thoughts. 

    Travis, thanks for your input as well. I'll have a look!

     

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 01/12/16
    Options

    Preston,

     

    Given the formatting change and not having to deal with unknowns, here's what I did:

     

    1. Reformatted the drop down list (example 4:00AM-00)

    2. Created two columns to get the value from the right two characters.

    - RIGHT() returns a text type value so we need VALUE() to turn it into a number.

    - these are only for clarity - I would lock and hide them at the end of testing.

    3. Total hours is then just as easy IF (start >= end, "error", (end-start)*0.25 )

     

    A little bit of design goes along way.

    Here's a picture.

     

     

    and here's a read-only publish link:

    http://publish.smartsheet.com/14a16691a7e34247afdfed00016ce3c3

     

    I can't guarantee that link will last for a long time, but it will be around for "a while".

     

    I'll also send you an editable version via e-mail. It will save time on the copy of the drop-down list at least.

     

    Email is jcwill23@gmail.com

     

    This is a much simpler problem than the one Kara solved.

     

    Hope this helps.

     

    Craig

     

     

    ShiftClock.jpg

  • Preston
    Preston ✭✭✭✭✭
    edited 01/12/16
    Options

    That, sir, is EXACTLY what I'm talking about! I won't be able to dig into this today, but this does appear to solve my problem! And it looks SO  much simpler than what I had envisioned. Thank you so much! SmileSmileSmile

  • Travis
    Travis Employee
    Options

    Good work Craig! 

     

    I wanted to show you Kara's formula in action - just to give you another option.

     

    The formula is located in the Hours column and will display the difference between StartTime and EndTime in a HH:MM format. This works down to the minute. In this example I manually added start and end times, but you can use this with a Dropdown list of times. In order for it to calculate correctly, you need to specify AM or PM using AM and PM or A and P. Here it is:

     

    https://app.smartsheet.com/b/publish?EQBCT=a820445951964445a795591bee66e3d1

     

    I can share the sheet with you if you would like - just let me know! 

  • Dorothy Ani
    Options

    Hi Travis, 

     

    Can you share the sheet with me if that's okay :) 

     

    Thank you! 

  • Tamara K
    Options

    First of all, thank you so much for this great solution. Craig, I've used your suggestions (dropdown with last 4 digits used to calculate total # of minutes) to successfully create a timecard sheet (yay!).

     

    Here's a tricky added requirement that is blowing my mind:
    I need to alert managers when their employees are approaching their max hours. Sounds simple, but the max hours is not based on the work week or number of days. Instead, any time an employee has 30 consecutive hours "off", their workweek essentially "resets" and they are eligible to work up to 60 max hours without another 30-hour break.

     

    Any bright ideas on how one could accomplish this?? Thanks, in advance for thinking about this conundrum!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    That does sound like an interesting problem.

    I'll add it to my list.

     

    Craig

  • Tamara K
    Options

    I appreciate it, Craig.

    I think I came up with a plausible (albeit VERY convoluted) solution. It hurts my head to even describe it! I'm just hoping it's not buggy.

  • Judi Le
    Options

    Hello Smartsheet friends

    i'm using the meeting agenda sheet. I want to add a start and end column for each activity for the day. 

    example, my first activity is meet and greet, it will start at 8:00am ends 9:00am.

    i need a column that calculates the amount of hour and/or minutes. i'm hoping there's a way to have time slot predecessors?

    How do i add the formula? what's the formula for that?

    My goal is to see if i have allotted enough time for each activity and see what time the day would end at with all my activities.

    thanks.

    Screen Shot 2018-03-16 at 9.32.06 AM.png

This discussion has been closed.