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

 

[SteveW]

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

Steve

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

 

[mr_teacher]

If you have all three cells set with the format of hh:mm in custo
format then when you do your subtraction the result will show the tim
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

Car

 

[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

 

[mr_teacher]

Hi,

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

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

=LEFT((C12+C15),LEN(C12)-3)&"hour
"&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 o
C15",LEFT((C12+C15),LEN(C12)-3)&"hour
"&ROUND(((C12+C15)-(LEFT((C12+C15),LEN(C12)-3)))*60,0)&"mins")
Format = general
Display = 7.8

 

[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 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

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