time conversion help

Discuss the spreadsheet application

time conversion help

Postby compparter » Thu Apr 17, 2008 6:00 pm

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
compparter
 
Posts: 2
Joined: Thu Apr 17, 2008 5:46 pm

Re: time conversion help

Postby Villeroy » Thu Apr 17, 2008 6:41 pm

Simply enter times (9:45) rather than integer numbers (945) and everything calculates well.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28643
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: time conversion help

Postby compparter » Thu Apr 17, 2008 7:12 pm

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..
compparter
 
Posts: 2
Joined: Thu Apr 17, 2008 5:46 pm

Re: time conversion help

Postby Villeroy » Thu Apr 17, 2008 7:58 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28643
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 28 guests