You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

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

User profile for user: BugDetector
BugDetector Author
User level: Level 1
10 points

Removing date stamp when adding time in a cell

Whenever I add a time to a cell, it automatically adds a hidden date stamp. This becomes a problem when I'm trying to calculate hours worked on a schedule.


So, for example if someone worked from 9:00 to 13:00, that would be 4 hours. I would have column "A" be 9:00, column "B" be 13:00, and column "C" would have an equation with the hours worked being 4 hours.


However, if I entered 9:00 in column "A" today, and then tomorrow I added the 13:00 in column "B", the total hours worked would equal 28 (4 hours plus 24 hours). When I edit the column A and B, I notice that there is a date stamp added.


Anyone know how to remove this functionality? I don't want any automatic date stamps added.


Thanks!

MacBook Pro 15″, macOS 10.14

Posted on May 11, 2021 2:31 PM

Reply
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Posted on May 11, 2021 6:09 PM

Two alternatives to Ian's solution:


Use TIMEVALUE to strip the date part and convert the time to a fraction of a 24 hour day. Do the subtraction and multiply the result by 24.


C2: (TIMEVALUE(B2)-TIMEVALUE(A2))*24


OR


If your times are in 15 minute blocks, and/or you have a fixed range of start and end times, set up a popup menu for the possible start times, and a second pop-up menu for the possible stop times.


The values in the pop-ups can be set to show only the time part, but will contain the full date and time value for each entry in the menu. If both menus and all of the choices on each are created on the same day, the menus will retain the 'same date' relationship with each other, making the difference a straightforward B-A formula.


Text version of the formula in C2 is shown in cell D2. The result from this formula is a number representing the number of hours (and decimal fraction of an hour between the time in A2 and the time in B2.


The formula image shown in D4 is the formula used in C5, and filled down to C8.


Cells A4 to A8 each contain a copy of the Start Time menu with the menu items displayedin front of row 9. The menu cell was filled down a feww extra rows to allow showing the menu without covering the values to which the menu had been set in rows 4 to 8).


Cells B4 to B8 contain similar pop-up menus showing the available Stop times.


The results in this part of column C are Duration values in hours and minutes.


Regards,

Barry

View in context

Similar questions

3 replies
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

May 11, 2021 6:09 PM in response to BugDetector

Two alternatives to Ian's solution:


Use TIMEVALUE to strip the date part and convert the time to a fraction of a 24 hour day. Do the subtraction and multiply the result by 24.


C2: (TIMEVALUE(B2)-TIMEVALUE(A2))*24


OR


If your times are in 15 minute blocks, and/or you have a fixed range of start and end times, set up a popup menu for the possible start times, and a second pop-up menu for the possible stop times.


The values in the pop-ups can be set to show only the time part, but will contain the full date and time value for each entry in the menu. If both menus and all of the choices on each are created on the same day, the menus will retain the 'same date' relationship with each other, making the difference a straightforward B-A formula.


Text version of the formula in C2 is shown in cell D2. The result from this formula is a number representing the number of hours (and decimal fraction of an hour between the time in A2 and the time in B2.


The formula image shown in D4 is the formula used in C5, and filled down to C8.


Cells A4 to A8 each contain a copy of the Start Time menu with the menu items displayedin front of row 9. The menu cell was filled down a feww extra rows to allow showing the menu without covering the values to which the menu had been set in rows 4 to 8).


Cells B4 to B8 contain similar pop-up menus showing the available Stop times.


The results in this part of column C are Duration values in hours and minutes.


Regards,

Barry

Reply

Removing date stamp when adding time in a cell

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