How do you split a list of dates/times into weeks?

Question

Level 1

1 points

How do you split a list of dates/times into weeks?

Hello all,

I am trying to make a time management spreadsheet that will keep track of my odd hours at my workplace. What I want to do is have one table with all of my date/times in and out, hours worked, and notes, that is then linked to a form. In a second table, I'd like to tablulate my week-by-week totals as well as monthly leave totals (sick/annual leave). What I can't work out is if you can make a table that automatically calculates a total number of hours for a week/month. So for example, if I have all my work from January through March in my first table, that the second table would be able to split that sheet into week-long sections and calculate the total number of hours worked for each week. Is such a thing possible in Numbers? Am I making sense? Thanks for the help!

MacBook Pro, Mac OS X (10.7.3)

Posted on Apr 14, 2014 1:44 PM

Reply

Answer 1

Best reply

SGIII

Level 8

35,324 points

Apr 14, 2014 1:58 PM in response to Lithium3fun

Yes you can do that quite easily in Numbers. SUMIF is your friend here:

In cell B2, copied down: =SUMIF(Timesheet::A,A2,Timesheet::C)

If you want totals by month, you could add a Month column and apply the same approach.

There are more complicated approaches using date math functions, but I find this the easiest.

SG

Reply

Answer 2

Lithium3fun Author

Level 1

1 points

Apr 14, 2014 2:33 PM in response to SGIII

Ah! I have no idea how I never saw that before.

While typing in SUMIF I noticed SUMIFS, which I think fits my needs well. My only problem is that I can't figure out how to use a reference in a condition along with an operator. So for example =SUMIF(Work Times::C2:C84,Work Times::A2:A84,>=A14,Work Times::A2:A84,<=B14) returns a syntax error. Any idea what I'm doing wrong?

I don't see how it's any different than Apple's own example:

Message was edited by: Lithium3fun

Reply

Answer 3

SGIII

Level 8

35,324 points

Apr 14, 2014 2:39 PM in response to Lithium3fun

=SUMIF(Work Times::C2:C84,Work Times::A2:A84,>=A14,Work Times::A2:A84,<=B14) returns a syntax error.

I won't have and opportunity to test here, but I think you need to add double quotes and concatenate, e.g.

>=A14

should be

">="&A14

etc.

And you need to use SUMIFS if you have more than one column-criterion pair.

SG

Reply

Answer 4

Apr 14, 2014 2:47 PM in response to Lithium3fun

Lithium3fun wrote:

...My only problem is that I can't figure out how to use a reference in a condition along with an operator. So for example =SUMIF(Work Times::C2:C84,Work Times::A2:A84,>=A14,Work Times::A2:A84,<=B14) returns a syntax error. Any idea what I'm doing wrong?

If you look carefully at the examples, you will see that the conditions are all text strings. You can convert your condition with a reference in it to a string by writing:

=SUMIF(Work Times::C2:C84,Work Times::A2:A84,">="&A14,Work Times::A2:A84,"<="&B14)

Regards,

Jerry

Reply