calculating time

[terraone]

gentlemen and scholars...I am looking for some advice. I have a spreadsheet where my crews log in and log out at each site. this is done in 24 hour format. I have a separate column cell where I would like to calculate the total time spent on site...or time out minus time in. preferable I would like this total to be in minutes. I can get a value in hours and minutes IF my time out is greater than my time in. for example...time in is 07:30...time out is 08:45 my total is 1:15. however,. if my time in is, say, 9:50 and my time out is 1:30 I get no value in my total cell...it says "dates and times are negative or too large to display as ######.

thanks in advance! oh...Office 2010

[James Keuning]

It sounds like the 1:30 should be 13:30 which is why you are having a problem.

locluxul.png

[pjwhitfield]

Please Login or Register  to view this content.

[terraone]

james...in reply to your comment...my 1:30 is an am time trying to subtract from a 9:50 pm. this is why I get a negative number

PJ...wow...I think you are relying on me knowing what I am doing! lol
I will try to implement your formulas into my spread sheet.

[MrShorty]

I would probably start here: http://www.cpearson.com/excel/datetime.htm to get an understanding of how Excel handles dates/times.

Since you want to be able to subtract times across midnight, my first thought would be to enter dates and times together. If you will have the start time entered as 31 Dec. 2014 9:35 PM and end time as 1 Jan 2015 1:30 AM (for example), it should be as simple as subtracting end time - start time to get "elapsed days" worked (since I don't expect any 24+ hour shifts, these numbers will come out as fractions of a day).

These results can be left as fractions of a day and displayed in the desired format (either elapsed hours or elapsed minutes -- see number formatting options) or can be converted to fractional hours or fractional minutes by multiplying the result by a suitable conversion factor. *24 hours/day to get fractional hours, *24 hours/day *60 minutes/hour= *1440 minutes/day to get fractional minutes. It all starts by understanding how Excel handles dates/times as fractional days.