Minutes

[Bobby]

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

[SteveW]

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

Steve

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

<[email protected]> wrote:

> Hi there,
>
> I have a timesheet which works out working hours e.g. Time in 8:52 tim=
e =

> 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 s=
how
> hours and minutes?
>
> TIA



-- =

Steve (3)

[mr_teacher]

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

[Bobby]

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

[mr_teacher]

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

[SteveW]

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 et=
c

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 =3D b16/24
format custom hh:mm

Steve

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

<[email protected]> 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=3Dtime in
> Format time format
> Displayed 8:52
>
> Cell b12=3D time out
> Format time format
> Display 12:00
>
> Cell C12=3D
> Formula
> =3DIF((OR(B12=3D"",B11=3D"")),0,IF((B12<B11),((B12-B11)*24)+24,(B12-B1=
1)*24))
> format =3D 0.00_);[Red](0.00)
> Display 3.13
>
> Cell b14=3Dtime in pm
> Format time format
> Display 12:30
>
> Cell b15=3D time out pm
> Format time format
> Display 17:14
> Cell C15 =3D
> Formula
> =3DIF((OR(B15=3D"",B14=3D"")),0,IF((B15<B14),((B15-B14)*24)+24,(B15-B1=
4)*24))
> Display 4.73
>
> Cell B16 =3D Total time worked
> Formula =3DIF(OR(ISTEXT(C12),ISTEXT(C15)),"Error in C12 or C15",(C12+C=
15))
> Format =3D general
> Display =3D 7.87
>
> It would be so great if someone could find me a solution



-- =

Steve (3)