Want to highlight a helpful answer? Upvote!

Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: Barathrum
Barathrum Author
User level: Level 1
8 points

Numbers Shift calculation.

Hello,


I am trying to replicate something that i did with some success in excel and i don't seem to be as successful.

Basically what i want is to keep note of all my shifts hours worked and a total by the end of the period.


I meddled with TIMEVALUE but that requires the date as well, which i don't want to include on the same cell and it complicates things for me.


I'm attaching a screenshot for reference, if anyone could help it would be greatly appreciated.


Thank you.

User uploaded file

MacBook Pro (Retina, 15-inch, Early 2013), macOS High Sierra (10.13.1)

Posted on Nov 11, 2017 12:08 PM

Reply
Question marked as Best reply
User profile for user: SGIII
SGIII
User level: Level 8
35,252 points

Posted on Nov 11, 2017 12:29 PM

At File > New in your menu are some interesting templates. The one called 'Employee Schedule' has a table similar to your screenshot.


User uploaded file


You could change the EMPLOYEE column to DAY.


The formulas used in the template are worth studying. Numbers has a data format called 'Duration', which Excel doesn't have. Whenever you subtract two date-times Numbers expresses the result as a Duration.


Numbers and Excel handle dates and times differently. Numbers stores dates as date-time strings, whereas Excel uses a serial number to represent a date (for most versions of Excel that serial number is the number of days since January 1, 1900). As you've noticed you can't have "just the date" or "just the time" in Numbers. But you can format a cell to display just the date or just the time.


SG

View in context
4 replies
Question marked as Best reply
User profile for user: SGIII
SGIII
User level: Level 8
35,252 points

Nov 11, 2017 12:29 PM in response to Barathrum

At File > New in your menu are some interesting templates. The one called 'Employee Schedule' has a table similar to your screenshot.


User uploaded file


You could change the EMPLOYEE column to DAY.


The formulas used in the template are worth studying. Numbers has a data format called 'Duration', which Excel doesn't have. Whenever you subtract two date-times Numbers expresses the result as a Duration.


Numbers and Excel handle dates and times differently. Numbers stores dates as date-time strings, whereas Excel uses a serial number to represent a date (for most versions of Excel that serial number is the number of days since January 1, 1900). As you've noticed you can't have "just the date" or "just the time" in Numbers. But you can format a cell to display just the date or just the time.


SG

Reply
User profile for user: SGIII
SGIII
User level: Level 8
35,252 points

Nov 11, 2017 12:52 PM in response to Barathrum

That was quick! Glad the template got you on the right track. Be sure to check out other templates there, too. They may not directly address what you are trying to do but they provide excellent examples of how things can be done "Numbers style," which can be different from what you are used to doing in Excel. Generally smallish, separate tables on the canvas of a sheet, rather than one big expanse of cells as in Excel.


SG

Reply

Numbers Shift calculation.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.
Learn more Sign up