using start and end time columns to calculate hours worked for a timesheet page

J. Craig Williams

For this data:

 

StartDate = 1/1/17, StartTime = 3:00 pm,

EndDate = 1/3/17, EndTime=  8:00am.

 

what is the right answer?

41 hours?

10 hours? 

11 hours?

 

Craig

 

J. Craig Williams

This formula will return a number based on the date and time.

For example 1.5

The 1 indicates the 1st day of the year and 0.5 is noon.

 

=YEARDAY(StartDate327) + IF(FIND("pm", StartTime327) > 0, (VALUE(LEFT(StartTime327, FIND(":", StartTime327) - 1)) + VALUE(MID(StartTime327, FIND(":", StartTime327) + 1, 2)) / 60) + 12, (VALUE(LEFT(StartTime327, FIND(":", StartTime327) - 1)) + VALUE(MID(StartTime327, FIND(":", StartTime327) + 1, 2)) / 60)) / 24

 

This will do the same for the end date

 

=YEARDAY(EndDate327) + IF(FIND("pm", EndTime327) > 0, (VALUE(LEFT(EndTime327, FIND(":", EndTime327) - 1)) + VALUE(MID(EndTime327, FIND(":", EndTime327) + 1, 2)) / 60) + 12, (VALUE(LEFT(EndTime327, FIND(":", EndTime327) - 1)) + VALUE(MID(EndTime327, FIND(":", EndTime327) + 1, 2)) / 60)) / 24

 

When you have those, you can:

1. Determine if the start is before the finish.

2. Determine how many consequetive hours are between them.

= (second formula - first formula) * 24

 

If the year's don't match, then if we can limit the number of expected overlap days to less than 180 then you could simplify the determination of over the new year hours by adding 180 to both start and end dates before you figure them out.

But you may still have an error around the leap-day if you aren't careful.

 

Combining the two formulas above into a single column should be easy.

 

Craig

Abdel Atia

Hello Travis ,

thank you very much! Dose this formula work on durations of more then 24 hours, different times on different dates (see attached).

Thank you in advance 

RichardPurchas

This is all great but do you have plans to incorporate dates & times (not just dates) into your leveling calculations?

I am used to MS Project where if I enter a start date/time as "01/05/2017 10:00am" and a duration of 1 hour then the finish date is calculated as "01/05/2017 11:00am". If I then have second task that is dependent on the above task (say Finish-to-Start) then the Start Date in the second task defaults to "01/05/2017 11:00am".

Can you please comment on your plans to incorporate "Time" as a first-class citizen for all date/time columns, in resource-leveling processes etc. Thanks.

Kal-El

I am receiving a Unparseable. I copied your formula and used the same rows. Can you copy and share your sheet? 

Kal-El

I am receiving a Unparseable. I copied your formula and used the same rows. Can you copy and share your sheet or a sheet with the formula in the cell? That way I copy from the cell directly 

J. Craig Williams

Tee Bee,

Who is this directed to?

Travis does not stop by here very often.

Craig

Guy

Thanks Travis i'm wanting to use this one as well but to record start and finish times for a cross country run where runners are starting in stages, to show who the fastest competitor was.  So i need seconds to be entered, calculated, and displayed.  I tried just added them in this format: 12:24:05 but the formula just ignores them and displays only the hours and minutes.  Any ideas gratefully received.  Thanks

loosefix

I want to input a start time, and an end time for a single date (daily input form).  This is possible? or do i need to be an expert coder?  Surely there is a field available for this??

J. Craig Williams

The Smartsheet developers are working on a TIME function. No release date has been announced.

Craig

Jeremy Walters

I Know this Thread is a little aged, but I was looking for a way to add time by converting the time stamp from a "Created Date" column to 24 hours so I could subtract time duration from another row (within the same given day). The application is to set up a working time clock for my company using smartsheet.  HR demanded punches rather than filling out a form at the end of the day.  I found out very quickly that this was more complicated than I had anticipated.  However, I was able to come up with a formula that works, based on the format of the time stamp (MM/DD/YY 12:12 PM).  I will then simply subtract the start from the end, and can even incorporate lunch hours. The formula would be much less complicated if the Timestamp format would read 01:30 instead of 1:30.  As it is, I had to account for every variable, including AM/PM hours.  

I will be incorporating this into a form so that I get auto feedback for each employee.  Just in case this helps anyone else in here with similar issues, I have pasted an example along with the formula below.

EX.:  The Morning clock-in timestamp for employee 'x' in Column A, R1= 11/10/17 6:22 AM, So, the formula in Column B,R1 comes up with 6.37; The evening clock-out timestamp in Column A,R2= 11/10/17 4:15 PM, So, the formula in Column B,R2 comes up with 16.25.  

This allows me to simply subtract the hours with a simple formula in another cell (16.25 - 6.37 = 9.88 hours for employee "x" on 11/10/17). 

Here's the formula: 

=IF(AND(LEN([Column A]1) = 16, RIGHT([Column A]1, 2) = "PM"), (VALUE(LEFT(RIGHT([Column A]1, 7), 1))) + (VALUE(LEFT(RIGHT([Column A]1, 5), 2)) / 60) + 12, IF(AND(LEN([Column A]1) = 16, RIGHT([Column A]1, 2) = "AM"), (VALUE(LEFT(RIGHT([Column A]1, 7), 1))) + (VALUE(LEFT(RIGHT([Column A]1, 5), 2)) / 60), IF(AND(LEN([Column A]1) = 17, LEFT(RIGHT([Column A]1, 8), 2) = 12, RIGHT([Column A]1, 2) = "PM"), 12 + (VALUE(LEFT(RIGHT([Column A]1, 5), 2)) / 60), IF(AND(LEN([Column A]1) = 17, LEFT(RIGHT([Column A]1, 8), 2) = 11, RIGHT([Column A]1, 2) = "PM"), 23 + (VALUE(LEFT(RIGHT([Column A]1, 5), 2)) / 60), IF(AND(LEN([Column A]1) = 17, LEFT(RIGHT([Column A]1, 8), 2) = 10, RIGHT([Column A]1, 2) = "PM"), 22 + (VALUE(LEFT(RIGHT([Column A]1, 5), 2)) / 60), IF(AND(LEN([Column A]1) = 17, LEFT(RIGHT([Column A]1, 8), 2) = 12, RIGHT([Column A]1, 2) = "AM"), 0 + (VALUE(LEFT(RIGHT([Column A]1, 5), 2)) / 60), IF(AND(LEN([Column A]1) = 17, LEFT(RIGHT([Column A]1, 8), 2) = 11, RIGHT([Column A]1, 2) = "AM"), 11 + (VALUE(LEFT(RIGHT([Column A]1, 5), 2)) / 60), IF(AND(LEN([Column A]1) = 17, LEFT(RIGHT([Column A]1, 8), 2) = 10, RIGHT([Column A]1, 2) = "AM"), 10 + (VALUE(LEFT(RIGHT([Column A]1, 5), 2)) / 60)))))))))

Al ISS

The formula from Travis works great but I added this formula in two different columns in my spreadsheet (only 16 colums) and, after entering only 9 data rows, the spreadsheet freeze and don't let add data anymore, does not matter if I using my desktop or any mobile device. I talked to Smartsheet technical support and they are looking to find out why this is happening. 

I've made some tests in a regular excel spreadsheet and, if Smartsheet create another pre-set column allowing you enter time and let you choose 24h or 12h like regular excel (Format Cells, Custom, "h:mm" or "h:mm AM/PM") we will be able to use a very simple "IF" formula to calculate time, specially if your hours needed are less than 24 hours.

=IF(K16-J16>=0,K16-J16,K16+"24:00"-J16) for Military time

K16= the later time

J16= the earlier time

 

The last part of the formula is used when the later time, numerically, is smaller than the earlier time. (Ex. 0:02 - 23:59= 0:03)

Hope Smartsheet can let us have time columns in the near future.

 

Al

 

 

Al ISS

41

prasadr_07

Hi,

My name is Ravi. I do have similar problem with calculating the total hrs worked by individual person.

I workout of India. Require your help in calculating the actual time worked in the office.

Eg. if a person start time is 8.30 AM and end time is 17.45 total hrs is 9.15..correct?

however if start time is 9.45 AM and end is 19.15 it should be 9.30, but it is giving me 9.70.

help me to get the exact time or let me know how to do this correctly.

jjpax

I know this post is a little old, but thankfully I found it and it has worked to solve the problem of calculating total time from two drop down lists of hours. Did you ever find a solution to the problem you were having with calculating time where the start time and the end time are on a different day?, because I'm am trying to solve the same problem. Thanks, John.