Page 1 of 1

time conversion help

PostPosted: Thu Apr 17, 2008 6:00 pm
by compparter
Hello all,

I have a problem and was hoping some math guru can help me.
Here's the thing. Where I work we schedule our people with an
excel style work sheet, we bring our people in on the tenths of the
hour or every 6 minutes.

Ex. start time 606, 612, 618, 642, .......
Also the people are not allowed to be on the clock for more
than 14 hours straight. Our sheet has a cell that is labled
max hours which adds 1400 to the value in the start time cell.

So start time 606 would yield 2006 max hours. This works fine
except when someone starts after 1000 ex. 1100 yields 2500
which in reality that is 100 the next day. Any thoughts on how to
do that conversion so it would show the 100 versus 2500.
As you can tell we do everything in military time.

:? i think the fomula might be g7+1400 if g7>2400 then g7-2400 :?
Can someone offer a formula for this?

Many thanks :D

Re: time conversion help

PostPosted: Thu Apr 17, 2008 6:41 pm
by Villeroy
Simply enter times (9:45) rather than integer numbers (945) and everything calculates well.

Re: time conversion help

PostPosted: Thu Apr 17, 2008 7:12 pm
by compparter
Villeroy wrote:Simply enter times (9:45) rather than integer numbers (945) and everything calculates well.


I tried but it does not add the 1400 correctly.
JEFF C##### 09:00:00 AM 345:00:00
This is the result that I get..
Could it be that 14 hrs doesn't add correctly to the 9:00 am start time. :? :?

I don't think I am relating my problem clear enough and for that I apologize.
Let me try to clarify the problem :oops:

If I issue a 6:00 a.m. start time then the latest that you can be on the clock
that day is 8:00 p.m. I would like to be able to display the time in a cell
labled "max hours" that would show the 8:00 p.m. time or 2000 hours would
be fine as well. The way we are doing it now works fine except when we start
someone past 11:00 a.m. because then we get max hours of over 2400 which
in essence means that they are clocking out after midnight. which is fine but
then you have to subtract the 2400 to get the am clock out time.

Ex. 11:00 am start time or 1100 + 1400 = 2500 - 2400 = 100 or 1:00 a.m.
That's where I am trying to get to.. just having a hard time getting there..

Re: time conversion help

PostPosted: Thu Apr 17, 2008 7:58 pm
by Villeroy
Hint: Times are fractions of days.
24:00 is just another way to enter number 1
12:00 is just another way to enter number 0.5
6:00 is just another way to enter number 0.25

Add 5 hours to A1:
=5/24+A1 and format the resulting number as you like.