Format a TIME image in a cell

E

Emerogork

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
 
M

Michael

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.
 
E

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?
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?
 
M

Michael

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top