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

[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

 

[Frank Kabel]

Hi
could you split these entry values in two cells (and then simply
subtract the values).

 

[boe]

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.

 

[boe]

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

 

[Frank Kabel]

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'

 

[Arvi Laanemets]

Hi

At first be sure start and end times are in time format.

=C19-B19+(C19<B19)

 

[boe]

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

 

[boe]

Thank you, I made sure to put the first two cells in a time format.

 

[Frank Kabel]

Hi
if you want to round your result use:
=ROUND((C19-B19+(C19<B19))*24*4,0)/(24*4)

 

[boe]

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

 

[Frank Kabel]

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

 

[boe]

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

 

[boe]

DOOH - I appreciate the help - this works exactly as you said it did, I got
the numbers backwards - THANKS AGAIN!!!!

 

[boe]

Thanks again!!

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

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