Removing date stamp when adding time in a cell

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

Hi BugDetector,

Format columns A and B as Text. Leave column C as Automatic.

Regards,

Ian.