time calculation

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

I'd like to count the total hours by one column deducted by another.

Format:
When I entered the time, I didn't put AM and PM into consideration. So
cell is considered AM. How can I change the whole column from AM to PM
without affecting the numbers? For examples, it shows 9:10 and it's
9:10pm instead of changing to 21:10 in cell?

How to calculate the hours between 7:04am to the 1:03am of the next
day? The deduction turns to ######.

Thanks for the help,
 
When I enter time code of video in a cell, it always think it's the
clock time. if I enter 34:44, it changes it to 10:44. How can I format
it so it's hh:mm:ss? I've tried entering mm:ss as custom format, but
it changed it 44:00. H:mm:ss requires entering extra 0: for hours.

Thanks for the help,
 
In an unused,empty cell enter
0.5

Right click in that cell and choose "Copy".
Select all the cells in that time column, and right click in that selection.
Choose <Paste Special> <Add> <OK>

NOW, while that column is *still* selected,
from the Menu Bar:
<Format> <Cells> <Number> tab,
Click on "Time",
And then select a time format from "Type" window that's to your liking,
Then <OK>,
And you're done!

Don't forget to clear the cell with the 0.5 in it.
 
If you entered 9:10 in A1 ( its actually 9:10 AM ), in B1 enter:

=A1+.5 and format as Time.
 
Forgot the second part of your question.

With start time in A18, and end time in B18, use this to calculate the
difference:

=MOD(B18-A18,1)
 
Its O.K. to format the cell as hh:ss, but you must still enter the value as
0:34:20 to get 34 minutes and 20 seconds.
 
Back
Top