Time

[Kelly]

I am trying to do a schedule and I am looking for an easy
way to input time and find scheduled hours.

Is there a format that will allow me to put in 1630 and it
come up as 4:30?

Also if I have 8:00 am in one cell and 4:30 in another and
I subtract them how can I get it to give me 8.5 as
scheduled hours?

Thanks!

 

[Norman Jones]

Hi Kelly,

See Chip Pearson's Time and Date entry page:

http://www.cpearson.com/excel/DateTimeEntry.htm

Ron de Bruin has aQuickDateEntry addin, freely downloadable at:

http://www.rondebruin.nl/qde.htm

 

[Myrna Larson]

No, you must input times with the colon. Without it, to Excel it's just a
number, not a time. Once you've entered the times correctly, to get the
difference in hours, =(B1-A1)*24 and format as general (8.5), or =B1-A1 and
format as a time (8:30)

 

[Myrna Larson]

Addendum: If you enter the times with a decimal point instead of a colon,
16.30 (but not 1630), you can use search and replace to replace the period
with a colon, thus converting them to times.

Otherwise, you need a formula in another column to convert to a time:

=TIME(A1/100,MOD(A1,100),0)

 

[Arvi Laanemets]

Hi

To calculate time for period over midnight, and period length less than 24
hours, use formula:
=B2-A2+(B2<A2)
where A2 contains start time and B2 end time. Format the result as time.
Or
=(B2-A2+(B2<A2))*24
when you want the number of hours as result instead of time.

 

[Biff]

Hi!

You can also *temporarily* set autocorrect to do the same
thing. Just remember to remove the autocorrect condition
once you're done.

Biff