0

I want to make a Excel File (Using Libre Office Calc) that calcuates the working time per day, but I'm struggling to get it right as I'd like to have it.

Example:

A1 = type in your Start time. eg: 0800  (!!Note: no colon use at typing )
B1 = type in your End time. eg: 1630  (!! Note: no colon use at typing )
C1 = B1-A1/100 = 8.3  (means 8 hours 30 mins)

Now I'd like to "convert" 8.3 (8 h 30 min)... into 8.5 format. And it should be dynamic, because C1 may could be 8.12 which should be a 8.2 Basically it should take the digit after dot and do /60*100.

I don't want to type colons at A1 and B1.

Here you can download the sample.

I hope it's somehwat clear what I'd like to do.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Barret
  • 7
  • 1
  • 4

1 Answers1

-1

Added: It is easier to convert to time and back. You'll probably need to format the cell as General as it will probably attempt to display as a time by default:

=24*(TIME(A2/100,MOD(A2,100),0)-TIME(A1/100,MOD(A1,100),0))

This is thanks to a colleague of mine, Hans @ eileenslounge.com

Andy G
  • 19,232
  • 5
  • 47
  • 69
  • Nice.. Thank you very much.. works good for me :) and for those who have German language installation like I had. =GANZZAHL((B1-A1)/100)+(B1-(A1+GANZZAHL((B1-A1)/100)*100))/60 – Barret Jul 25 '13 at 07:52
  • I just got back working on the file and I noticed the formula has a bug. It works with a Starttime(A1) from X.00 - X.30 If you use X.45 as example you'll get a wrong result. – Barret Aug 08 '13 at 14:28
  • The values shouldn't contain dots, so perhaps you can provide a couple of specific values for A1 and A2 that give incorrect results. – Andy G Aug 08 '13 at 16:52
  • For example A1=845 A2=1630 =8.411 which is wrong. The formula works if you have A2=1630 and as A1 a number between 800 to 830 – Barret Aug 09 '13 at 06:41