calculating time on 12 hour clock

  • Thread starter Thread starter Ken Curis
  • Start date Start date
K

Ken Curis

I am trying to create a spreadsheet that will calculate
hours worked on a 12 hour clock. For example, I want to be
able to enter this into a single cell: 10am-4pm, and have
the result be "6" hours. The only way I can think of doing
it is to create a seperate table that "matches" whatever I
type into the field to a known answer in the matching
table. Can you think of an easier way?
Regards,
Ken
 
HI:
I didn't see that you wanted to use a one cell entry
before writing this post so Ill post it anyway someone
else may be curious.

here is one I use as a time clock.

In this example you will need to use the cells I
referance until you see how it works.

"for your entry"

use d7(h)-e7(m)-f7(am or pm) and d8-e8-f8

Place this formula anywhere in AH7

=IF(AND(D7=0,E7=0),0,IF(AND(D7=12,F7="pm"),((D7*60)+E7),IF
(AND(D7=12,F7="am"),(E7),IF(F7="pm",((D7*60)+E7+720),
(D7*60)+E7))))

Place this formula anywhere in AH8

=IF(AND(D8=0,E8=0),0,IF(AND(D8=12,F8="pm"),((D8*60)+E8),IF
(AND(D8=12,F8="am"),(E8),IF(F8="pm",((D8*60)+E8+720),
(D8*60)+E8))))

in a results cell place =(AH8-AH7)/60

good luck
 
You could use excel's "timevalue" function if you modify
the way you are storing the time in a cell. Timevalue
needs a space between the numbers and the am/pm bit ie
store it as "10 am-4 pm". (If you need half hours then
write it in the standard excel time format such as
as "10:30 am-4:30 pm").

Assuming the above is in cell A30, this formula would give
you the right answer (ie 0.25, or 6 hours if you format
the cell to display time)

=TIMEVALUE(RIGHT(A30,LEN(A30)-FIND("-",A30)))-TIMEVALUE
(LEFT(A30,LEN(A30)-FIND("-",A30)+1))

If you have a situation like "10 pm-4 am" though, the
above formula will return -0.75, so suggest you use mod to
avoid this;

=MOD(TIMEVALUE(RIGHT(A30,LEN(A30)-FIND("-",A30)))-TIMEVALUE
(LEFT(A30,LEN(A30)-FIND("-",A30)+1))+1,1)

The above should always return the right answer.

It's still possible to use the above formula with the way
you're storing your data now, but you'd have to have an
intermediate series of cells to add the spaces

ie something like

(in b30) =SUBSTITUTE(A30,"a"," a")
(in c30) =SUBSTITUTE(b30,"p"," p")
(in d30) =MOD(TIMEVALUE(RIGHT(A30,LEN(A30)-FIND("-
",A30)))-TIMEVALUE(LEFT(A30,LEN(A30)-FIND("-",A30)+1))+1,1)

Peter
 
Back
Top