Here's a fun one...

Preston

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

 

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 

J. Craig Williams

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

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...

 

Preston

J. Craig Williams

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

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

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

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

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

Seriously, thanks for entertaining my thoughts. 

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

 

 

J. Craig Williams

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

 

 

Preston

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! 

Travis

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

Hi Travis, 

 

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

 

Thank you! 

Tamara K

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

That does sound like an interesting problem.

I'll add it to my list.

 

Craig

Tamara K

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

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.