Hi there,

I have a timesheet which works out working hours e.g. Time in 8:52 time out
12.00 the answer is 3.13 so I then manually calculate the minute bit 0.13*60
which equals 8 minutes. Is there any clever way to calculate this to show
hours and minutes?

TIA
--
Bobby

a2 = 8:52
b2 = 12:00
c2 = b2-a2

Steve

On Thu, 10 Aug 2006 15:06:02 +0100, Bobby
wrote:

Hi there,

I have a timesheet which works out working hours e.g. Time in 8:52 time
out
12.00 the answer is 3.13 so I then manually calculate the minute bit
0.13*60
which equals 8 minutes. Is there any clever way to calculate this to show
hours and minutes?

TIA



--
Steve (3)

If you have all three cells set with the format of hh:mm in custom
format then when you do your subtraction the result will show the time
in hours and minutes. Also you need to make sure that you use the :
symbol to seperate your hours and minutes.

Hope that helps

Regards

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=570355

Hi Steve and Carl,
I tried your suggestions on my spreadsheet and got strange answers so tried
them on a blank worksheet and it worked fine so thank you!
I think the problem I have is that the cells have been formatted differently
to work with the formulae in them. The worksheet is a template from microsoft
and the workings are listed below.
Help!

Cell b11=time in
Format time format
Displayed 8:52

Cell b12= time out
Format time format
Display 12:00

Cell C12=
Formula
=IF((OR(B12="",B11="")),0,IF((B12<B11),((B12-B11)*24)+24,(B12-B11)*24))
format = 0.00_);[Red](0.00)
Display 3.13

Cell b14=time in pm
Format time format
Display 12:30

Cell b15= time out pm
Format time format
Display 17:14
Cell C15 =
Formula
=IF((OR(B15="",B14="")),0,IF((B15<B14),((B15-B14)*24)+24,(B15-B14)*24))
Display 4.73

Cell B16 = Total time worked
Formula =IF(OR(ISTEXT(C12),ISTEXT(C15)),"Error in C12 or C15",(C12+C15))
Format = general
Display = 7.87

It would be so great if someone could find me a solution
--
Bobby

Hi,

Not an expert on macros, but using just the formulas I have got this to
work if this is what you want - it has only changed the last part of
your macro as it stood. I have just added my formula in here so don't
know if it will work or not.

If this is not working then you could delete the last section of your
macro and in cell B16 put the formula

=LEFT((C12+C15),LEN(C12)-3)&"hours
"&ROUND(((C12+C15)-(LEFT((C12+C15),LEN(C12)-3)))*60,0)&"mins"

Hope this works

Regards

Carl

Cell b11=time in
Format time format
Displayed 8:52

Cell b12= time out
Format time format
Display 12:00

Cell C12=
Formula
=IF((OR(B12="",B11="")),0,IF((B12<B11),((B12-B11)*24)+24,(B12-B11)*24))
format = 0.00_);[Red](0.00)
Display 3.13

Cell b14=time in pm
Format time format
Display 12:30

Cell b15= time out pm
Format time format
Display 17:14
Cell C15 =
Formula
=IF((OR(B15="",B14="")),0,IF((B15<B14),((B15-B14)*24)+24,(B15-B14)*24))
Display 4.73

Cell B16 = Total time worked
Formula =IF(OR(ISTEXT(C12),ISTEXT(C15)),"Error in C12 or
C15",LEFT((C12+C15),LEN(C12)-3)&"hours
"&ROUND(((C12+C15)-(LEFT((C12+C15),LEN(C12)-3)))*60,0)&"mins")
Format = general
Display = 7.87


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=570355

This looks as though you actually wanted decimal hours anyway.

time in/out - lunch - time in/out

All input (time) is entered hh:mm
all output (time in hours)

Presumably you wanted a time (in hours) to enable pay caclualtion etc etc

So in the end those forumula look correct

If you want to see that decimal hours as hh:mm
then add an extra colum
b17 = b16/24
format custom hh:mm

Steve

On Thu, 10 Aug 2006 22:04:02 +0100, Bobby
wrote:

Hi Steve and Carl,
I tried your suggestions on my spreadsheet and got strange answers so
tried
them on a blank worksheet and it worked fine so thank you!
I think the problem I have is that the cells have been formatted
differently
to work with the formulae in them. The worksheet is a template from
microsoft
and the workings are listed below.
Help!

Cell b11=time in
Format time format
Displayed 8:52

Cell b12= time out
Format time format
Display 12:00

Cell C12=
Formula
=IF((OR(B12="",B11="")),0,IF((B12<B11),((B12-B11)*24)+24,(B12-B11)*24))
format = 0.00_);[Red](0.00)
Display 3.13

Cell b14=time in pm
Format time format
Display 12:30

Cell b15= time out pm
Format time format
Display 17:14
Cell C15 =
Formula
=IF((OR(B15="",B14="")),0,IF((B15<B14),((B15-B14)*24)+24,(B15-B14)*24))
Display 4.73

Cell B16 = Total time worked
Formula =IF(OR(ISTEXT(C12),ISTEXT(C15)),"Error in C12 or C15",(C12+C15))
Format = general
Display = 7.87

It would be so great if someone could find me a solution



--
Steve (3)