Time sheets with Numbers program

The best way to do this one is as follows:

Cell A1: 8:00
Cell A2: 14:00
Cell A3: 14:30
Cell A4: 17:00

In result cell, type:
=(-HOUR(A1) 60-MINUTE(A1)HOUR(A2)*60+MINUTE(A2)-HOUR(A3)*60-MINUTE(A3)+HOUR(A4)60MINUTE(A4))/60

The result will be 8.5 ... if you want to display in hours:minutes, then it gets a bit more complicated ... assuming you don't want to waste a cell, type:

=CONCATENATE(ROUNDDOWN((-HOUR(A1) 60-MINUTE(A1)HOUR(A2)*60+MINUTE(A2)-HOUR(A3)*60-MINUTE(A3)+HOUR(A4)*60+MINUTE(A4))/60,0),":", ((-HOUR(A1)*60-MINUTE(A1)+HOUR(A2)*60+MINUTE(A2)-HOUR(A3)*60-MINUTE(A3)+HOUR(A4) *60MINUTE(A4))/60-ROUNDDOWN(B4,0))60)

It works,

the result is 8:30

May I suggest that you try this in FileMaker (or any other database application). That way you can mantain separate staff and time sheet files but create a relationship between them to record hours worked.
With FileMaker you can also format the result field to display HH:MM:SS or HH:MM or HH. You can also format the input fields to display time as a.m. and p.m or straight 24 hour notation.
You can also create more subtle control over access.
(For those who are interested FileMaker will import from Numbers. But you have to export the numbers file as a CSV first. FileMaker will, on the other hand, open and convert an Excel file directly.)

Please excuse my butting-in, but it seemed silly to start a similar thread, so perhaps you can help me?

I have a much simpler timesheet, I just have an in time (A1) and out time (B1) and a total (C1):

In | out | total
9:00 | 12:00 | 3:00

Up until this point I have been manually filling out the in and out and mentally tallying the time and entering it in the total, because I couldn't figure out how to get Excell to do it for me. I now want to do it in numbers and see it is do-able. I tried to apply the one above but wasn't sure what to edit to get it to only tally one in time and one out. If you could help me out that would be great!

A.

If you can live with decimal results, e.g. 5.5 hours meaning 5 hours and 30 minutes, your formula would read simply:
=(B2-B1)*24
Where B2 would be check-out and B1 check-in time.
This works because Apple handles time in a way of fractions of 24.

crazierthanu wrote:
The best way to do this one is as follows:

Cell A1: 8:00
Cell A2: 14:00
Cell A3: 14:30
Cell A4: 17:00

In result cell, type:
=(-HOUR(A1) 60-MINUTE(A1)HOUR(A2)*60+MINUTE(A2)-HOUR(A3)*60-MINUTE(A3)+HOUR(A4)60MINUTE(A4))/60

The result will be 8.5 ... if you want to display in hours:minutes, then it gets a bit more complicated ... assuming you don't want to waste a cell, type:

=CONCATENATE(ROUNDDOWN((-HOUR(A1) 60-MINUTE(A1)HOUR(A2)*60+MINUTE(A2)-HOUR(A3)*60-MINUTE(A3)+HOUR(A4)*60+MINUTE(A4))/60,0),":", ((-HOUR(A1)*60-MINUTE(A1)+HOUR(A2)*60+MINUTE(A2)-HOUR(A3)*60-MINUTE(A3)+HOUR(A4) *60MINUTE(A4))/60-ROUNDDOWN(B4,0))60)

It works,

the result is 8:30

why not just =((a2-a1)+(a4-a3))*24
Worked for me.

Jason

Message was edited by: jaxjason

Yea, that's how I have it now. I put the same formula in both Excel and Numbers. Excel is happy to show the result as a fomatted time, but Numbers insists on showing as a decimal. Is there no way to get numbers to show it as time?

jaxjason wrote:

why not just =((a2-a1)+(a4-a3))*24
Worked for me.

Jason

For me will work too, but I have one more problem.

I have Time In, Time Out, and Lauch Time.
The formula I need is =((TI+LT)-TO)*24 or someting like that?

But Numbers won't many hours in a formula.

Thaks
Narcis

Hello

There is a trick to include your launch time value.
Define a time0 cell containing 00:00

Look at the screenshot.
It would have been better to write $B$2 but this kind of absolute reference is buggy in the french version



Yvan KOENIG (from FRANCE lundi 3 septembre 2007 8:04:31)

Hello Yvan

I tried your trick but something is wrong.

http://homepage.mac.com/narcis/1.jpg

Thank you

I find it incredible that Apple have released a version of numbers that does not have the ability to do time calculations, If like me you hate Excel and would love to use a more intuitive program this is a massive disappointment

Some of the solutions to this problem that are in this thread may appear to give a solution but on close examination they do not always give the correct answer.

There are four deferent problems hear
1) all time calculations return a decimal integer were 1 is a day and the numbers after the decimal point are the fractions of the day
2) The functions available in Numbers cannot operate on a calculation i.e. MINUTE (C4-B4) extracts the Minutes from C4 only
3) We are dealing with Modulo arithmetic hear i.e 60 minutes plus 1 minute is not 61 minutes but I hour and 1 minute
4) To my mind you need the results of any calculation to return leading 0 were appropriate otherwise the results will look a mess i.e
1:1:1 may be above
10:10:10 not good

1) To solve this you must do all HOUR MINUTE and SECOND extraction on the full result of the calculation not on the remainder
2) You must do the actual calculation in its own cell not as part of the formula
3) This is solved by 1
4) You need an IF statement to insert the leading 0

So if Column C is the start time and D the end time E the calculation D-C then to convert this number back to a string that looks like a time format you need this :
=IF(((E4) 24)<10,"0"&INT((E4)*24),INT((E4)24))&":"&IF((MINUTE(E4))<10,"0"&MINUTE((E4)),MINUTE((E4)))&":"&IF((SECOND(E4))<1 0,"0"&SECOND((E4)),SECOND((E4)))

This cannot deal with negative times If you need to deal with this you will have to identify a negative number i.e. >0 multiply it by -1 and then have a new IF statement to put it back in before the hour result

Apple need to update this program so it can do these calculations simple based on the arguments of the calculation because this result is a string and you cannot there for do further calculations using this result

It's not just Apple, Every spreadsheet I have worked with you had to learn the tricks to using the time and date functionality. Excel is a b^%$ to get time things done like this. REally ***** until you figure it out. The problem is how they store the time and deal with it. Its never HH:mm:ss. Its always number of deciaml seconds past midnight, or the number of decimal days since a certain date.

Each program handles it a little differently, but once they open up scirpting and add-ons, I think we will see many of these get taken care of by us, the users.

Happy numbers,
Jason

Hello i don't understand what you get.

On my machine it works well.



Yvan KOENIG (from FRANCE mardi 4 septembre 2007 22:32:40)