Excel: multiplying money with time

Posted:
in Mac Software edited January 2014
Hello.



Since I have a new night job, I wanted to keep track of all the hours I spent at work via an Excel spreadsheet.



I have one column for date, one for start time, one for end time, one for the duration and one for the earned money.



I managed to set the formula for the duration, as it proved a little bit difficult when you work from 22:00 to 7:00 or something like this. But I managed it okay (24 minus start time and then plus end time), even if it is not 100 percent correct. Is there a better way with working times like 23:45 to 9:15 or so?



But the most difficult is to multiply the hourly rate with the hours I spent at work.

It isn't as simple as Rate X Hours, or ( Hours / 60 ) > minutes > Minutes * (Rate/60) - even if it is redundant.



Has anybody any idea?



Thanks in advance...

Comments

  • Reply 1 of 2
    kennywrxkennywrx Posts: 141member
    A1 = start date (format this as "date" via format cell ex. mm/dd/yyyy hh:mm:ss)

    A2 = end date (format this as "date" via format cell ex. mm/dd/yyyy hh:mm:ss)

    A3 = A2 - A1 [time difference] (format this as --> custom --> "hh:mm")

    A4 = hour(A3) [hours in A3] (format this as number)

    A5 = minute(A3) [minutes in A3] (format this as number)

    A6 = A4 + (A5/60) [hours + minutes converted to hours] (number)

    Multiply decimal worked hours by the pay rate.
  • Reply 2 of 2
    mydomydo Posts: 1,888member
    Yup. In Excel it's best to use their date/time format. It's the number of days since 1/1/1900 in decimal format. When you do arithmetic on dates it gives you days and you convert that to hours or minutes.



    I'd change the above to.



    A3=(a2-a1)*60 = # of hours worked.



    A4=A3*Rate = (amount of pay for that day)



    or better yet



    A4=(a2-a1)*rate
Sign In or Register to comment.