Timesheet - Time (and obligatory date) entry - how do I enter time and get it to use date from another cell?

Can be done in a third cell, but not in the cell where you enter only the time.

The adjustment requires a formula in the cell that is to contain the result.

Entering any value into that cell immediately replaces whatever was in the cell previously—such as the formula required to produce the result you want.

Here is an example that returns the result as a number representing the number of hours work time recorded.

The formula considers only the TIMEVALUE of each date and time value, does the usual subtraction of each in time from the next out time, and addition of the two results. The sum is a decimal value representing the fraction of a day (24 hrs) spent on the job. It is multiplied by 24 to return tha value as a number of hours (and fraction of an hour).

Restrictions: All times must occur within the same day. As written, the formula will NOT return correct results for shifts beginning on one date and ending on the next date.

Entering time values only in all four cells will not affect the results. Ive shown te\he full data and time value in each cell to illustrate the actual values resulting from entering Date and Time in B2 and time part only in the other cells in row 2.

The unformatted parentheses are requires to ensure that the addition and subtraction they enclose is cone before the multiplication of the result by 24.

Regards,

Barry

Hi Wally,

If all of the time values are entered on the same day, their date parts will all be the same, and will not distort the final result.

If the date part is entered in a separate cell, it will be saved as 00:00:00 on that date. Provided it is used as a 'label,' and not included as a cell reference in the calculations that date will have no effect of the calculations or their result.

The result of the calculations will be either a duration in hours and minutes or a number representing a duration of that many hours plus a (decimal) fraction of an hour.

Here's the straight ahead version (row 3), with a result in hours.

All data in Row 3 was entered on May 2, Format for this row of cells is the default (Automatic).

Row 2 is from the table as shown in my earlier post.

Formula in F3: E3−D3+C3−B3

The only issue I see here is the lack of differentiation between times before and after noon. Delaying lunch to 1:15 leads to this result:

Regards,

Barry

HI Wally,

I received a note today from Yellowbox, reminding me of another property of Numbers that might prove useful here.

Numbers is quite talented at recognizing text showing what could be interpreted as 'time of day' and using that in a formula.

The advantage, in your case, is that entering the 'times' as text removes te possibility of specifying a different day for one or more of the Date & Time value used in the calculations. An example is shown in the 'new' row of my example table:

Rows 2 and 3 are discussed in my previous posts.

In Row 4, column A's data format is set to Automatic. The Date entered as "May 1" creates a date and time value (May 1, 2020 00:00:00) but displays only what was entered. While this cell could have been set to Text, the 'extra work is not needed as the value is not used in any formula.

Cells B4 to E4 have their data format set to Text, as can be seen by the default text alignment to th e left side of the cell.Even though they are now "text" values, not the Time part of Date and Time values, Numbers is able to recognize them as Time of Day, and to treat them as such in a formula.Note that the am-pm issue identified in row 3 still applies here, so you will need to add the am or pm designation to the 'times' to ensure correct results. (I did check after posting, and found that using a or p in place of am and pm worked just as well.

The result in these cases is a Duration, not a number, so depending how you used that value in further calculations, you may need to wrap formula in one of the DUR2 functions to convert the duration to a number.

Regards,

Barry

Hi Barry,

Now that your reply has been marked as Apple recommended, I feel free to butt in with a 24 hour clock solution that may help other users.

Columns A to E are formatted as Text. But as you say, Column A does not feature in any formulas, so it could be formatted as Automatic.

The red error triangles are a problem that we can deal with by using the IFERROR function (if, indeed, that is a problem!)

Thank you for always posting here, and stay safe.

Kind regards,

Ian.