Single cell hour calculation 1:15PM -7:30PM

  • Thread starter Thread starter boe
  • Start date Start date
B

boe

Is it possible for me to easily display a cell that looks like
1:15PM -7:30PM but then have a calculation cell next to it that shows 6.25
hours?

Thank you
 
Hi
could you split these entry values in two cells (and then simply
subtract the values).
 
Actually I'd be happy if I can do any hourly math that doesn't force me to
display the numbers in military time. Two columns to one for time in and
one for time out would be acceptable.
 
Thanks - I set it up like this
=HOUR(C19-B19)
But the formula didn't work for me when the the shift started at 1:30PM on
Monday and extended to 6AM on a Tuesday I get a #num!
1:30 PM 6:00 AM
 
Hi
use
=C19-B19+(C19<B19)

and format this value as 'Time'. If you need a decimal
value use:
=(C19-B19+(C19<B19))*24
and format as 'Number'
 
Thank you I appreciate the help. Sorry I should have given you the exact
goal to start with. I think I'm going to need an if then or something so
that I can use the same formula for a series of numbers
1:30AM - 10:00AM = 8.50
1:10PM - 9:00PM = 7.45
10:20PM - 9:00AM = 10:45
etc.
I'm trying to make a time sheet that will give me results rounded to the
nearest 15minutes
 
Thanks but I'm doing something wrong - this is what I got in my first test
using that formula

Time In Time Out Total hours

3:00 AM 7:30 AM 0.1875
3:00 PM 10:00 AM 0.791667
1:00 PM 10:00 PM 0.375
 
Hi
nearly there :-)
just format the total houers with a time format.
If you want to sum this column you may format the resulting cell with
the custom format
[hh]:mm

the [] brackets prevent a rollover after 24 hours
 
Sorry - I forgot to change the cell to hours but the last number comes up
wrong - 1PM to 10PM should be 9 hours I'm trying to figure out a cell that
can cover both single and double shifts.

Time In Time Out Total hours

3:00 AM 7:30 AM 4:30
3:00 PM 10:00 AM 19:00
1:00 PM 10:00 PM 15:00
 
DOOH - I appreciate the help - this works exactly as you said it did, I got
the numbers backwards - THANKS AGAIN!!!!
 
Thanks again!!

Frank Kabel said:
Hi
nearly there :-)
just format the total houers with a time format.
If you want to sum this column you may format the resulting cell with
the custom format
[hh]:mm

the [] brackets prevent a rollover after 24 hours

--
Regards
Frank Kabel
Frankfurt, Germany

boe said:
Thanks but I'm doing something wrong - this is what I got in my first test
using that formula

Time In Time Out Total hours

3:00 AM 7:30 AM 0.1875
3:00 PM 10:00 AM 0.791667
1:00 PM 10:00 PM 0.375
Click to expand...
Click to expand...
 
Back
Top