Auto Calculating Timesheet in Numbers

Question

Level 1

5 points

Auto Calculating Timesheet in Numbers

Hi,

I am trying to transfer over from an Excel auto calculating timesheet to a Numbers version but I cannot for the life of me convert the formula:

Day	Date(s)	Time In	Lunch Start	Lunch End	Time Out	Hours Worked
Wednesday	01/11/2017	9:30 AM	12:30 PM	1:00 PM	3:00 PM	5.00

This is the excel formula used to calculate the "5" in column G

=IFERROR(IF(COUNT(TimeSheet4[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Lunch End]]+[@[Lunch Start]]-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)

Can anyone help? This is not a formula I created it was already done on a template I have got.

I have looked at the Business template in Numbers for Employee Schedule but it is not suitable.

Thanks in advance!

Corry

iPad 2, iOS 5

Posted on Nov 27, 2017 9:56 AM

Reply

Answer 1

SGIII

Level 8

36,164 points

Nov 27, 2017 10:34 AM in response to corrinna

corrinna wrote:

Hi,

Day Date(s) Time In Lunch Start Lunch End Time Out Hours Worked

Wednesday 01/11/2017 9:30 AM 12:30 PM 1:00 PM 3:00 PM 5.00

I have looked at the Business template in Numbers for Employee Schedule but it is not suitable.

Here's the relevant table from the Employee Schedule template.

As you can see it's almost identical. I suspect it can be easily adapted to your needs. Perhaps you could explain why it's not suitable, and we can perhaps suggest how you might adapt it.

BTW, that complicated looking formula in Excel results from using an Excel Table. Numbers tables tend to be much easier to use, with cleaner formulas.

SG

Reply

Answer 2

corrinna Author

Level 1

5 points

Nov 30, 2017 2:56 AM in response to SGIII

Hi,

Thanks for your response. I guess why I thought it wasn't suitable is that I could not adapt it to incorporate the lunch breaks. I will have another look and see if I am able to do this.

Reply

Answer 3

SGIII

Level 8

36,164 points

Nov 30, 2017 6:50 AM in response to corrinna

The template explicitly incorporates a lunch break. You can vary how long that break is in the table at the top.

SG

Reply