Calculate wages from hours:minutes?

  linden 17:43 18 May 2003
Locked

In Excel 2000. Have some time sheets showing time spent. Formatted as elapsed time in hours:minutes. ( eg [hh]:mm shows 03:30 ) Now I want to multiply by an hourly rate eg £8.50

Any ideas - I just get strange answers. Excel seems to be trying to use its stored date/time references. Anyone know how to convert a time in hours:minutes to just minutes. Not format - that just shows us humans - the PC still fixates on its number.

Linden

  MartinT-B 17:59 18 May 2003

I don't use Excel - lotus instead.

I think you need to write a Macro/Formulae to get it to do it correctly. It's prolly trying to do everything in decimal (10) which is fine for £ & p, but minutes are 60 in an hour and hours are 24 in a day.

  VoG™ 18:10 18 May 2003

Say your Hours are in A1 and pounds per hour is in B1, then in C1 enter

=24*VALUE(A1)*B1

and format C1 as a number.

  linden 18:31 18 May 2003

VoG™

Excellent - that seems to work on a quick trial - I'll extend that now to the full spreadsheet!

Thank you VoG™ - any chance of a short explanation - I presume putting it into days is the heart of it.

  linden 18:36 18 May 2003

VoG™

Excellent - that seems to work on a quick trial - I'll extend that now to the full spreadsheet!

Thank you VoG™ - any chance of a short explanation - I presume putting it into days is the heart of it.

  VoG™ 18:53 18 May 2003

The explanation that Excel stores times internally as fractions of a day (i.e. 24 hours). Thus multiplying an Excel time by 24 converts it to hours. You need to ensure that the results cell is formatted as a number for obvious reasons.

I used the VALUE() conversion in case you had applied a custom format to get your times displayed. You may find that it is unnecessary and the following should work:

=24*A1*B1

  linden 19:21 18 May 2003

to VoG™

I did have a custom format that is used for adding the hours:minutes and to help the people entering data on timesheets. Your system seems fine and I can adapt now you have given me the 24 hour clue!

A short while back you pointed me to a Knowledge base item about the Excel problem with mail merge and named ranges. This told me what the problem was even if it was not possible to solve it. On the principle of working around a problem we added codes to the Excel data and then used the query facility in Word to find the data sets. Also saved changing the Excel spredasheet path each time. So thanks for that also.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?