I want a real simple display of time in a cell. I am making a sheet to show
the schedule of times for employees. When 7:00am is entered, I want just a 7
to appear. Same goes for 3:00pm, it is to show just the number 3.. I still
want to be able to calculate the numbers of hours from this cell so it has to
use the original value in the cell ( 7:00am to 3:00pm is 8 hours.)

In a different case, I wish to insert the start time (7am) and then enter the
number of hours assigned (8). The cell for the "time assigned cell" is to
show the time the employee is leaving (3).

No one works anything other than either 4 or 8 hour shifts and everyone
starts on the hour. No minute values are ever needed.

Emerogork

To solve the first issue select the cell or column where the time is, then
from the Main Toolbar Menu, select:
Format - Cells;
by default you should be in the number tab, if not just click on it.
Click on Custom and key-in an "h" no quote marks this will display your time
as 7

To solve the second issue do this:
Assume On A1 is the 7 we just formatted but it remains as 7:00 am in the back
On B1 is where you are keying in your 8 hours or whatever
On C1 You need the Following Formula:
=Time(Hour(A1)+B1,0,0)
This will return on C1: 3:00 pm, therefore you can follow the solution to
the first issue on this cell to display 3 only if that is what you whish.



--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Emerogork" wrote:

I want a real simple display of time in a cell. I am making a sheet to show
the schedule of times for employees. When 7:00am is entered, I want just a 7
to appear. Same goes for 3:00pm, it is to show just the number 3.. I still
want to be able to calculate the numbers of hours from this cell so it has to
use the original value in the cell ( 7:00am to 3:00pm is 8 hours.)

In a different case, I wish to insert the start time (7am) and then enter the
number of hours assigned (8). The cell for the "time assigned cell" is to
show the time the employee is leaving (3).

No one works anything other than either 4 or 8 hour shifts and everyone
starts on the hour. No minute values are ever needed.

Emerogork

Thanks to everyone with your quick and insightful responses. I have made
great progress in my project.

Can anyone help me refine this one?

Michael wrote:
To solve the first issue select the cell or column where the time is, then
from the Main Toolbar Menu, select:
Format - Cells;
by default you should be in the number tab, if not just click on it.
Click on Custom and key-in an "h" no quote marks this will display your time
as 7

3:00pm becomes 15.

Almost there, can I get 15 to show up as 3?

Try a variation of the formula I gave you like this:
=LEFT(TEXT(TIME(HOUR(A1)+B1,0,0),"h AM/PM"),2)

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Emerogork" wrote:

Thanks to everyone with your quick and insightful responses. I have made
great progress in my project.

Can anyone help me refine this one?

Michael wrote:
To solve the first issue select the cell or column where the time is, then
from the Main Toolbar Menu, select:
Format - Cells;
by default you should be in the number tab, if not just click on it.
Click on Custom and key-in an "h" no quote marks this will display your time
as 7

3:00pm becomes 15.

Almost there, can I get 15 to show up as 3?