Calculate wages from hours:minutes?

  linden 17:43 18 May 03
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 03

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 03

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 03

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 03

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 03

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 03

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.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

iPhone 9 and beyond: 32 amazing future smartphone developments - graphene, supercapacitor…