or Connect
AppleInsider › Forums › Software › Mac Software › Excel: multiplying money with time
New Posts  All Forums:Forum Nav:

Excel: multiplying money with time

post #1 of 3
Thread Starter 
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...
post #2 of 3
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.
post #3 of 3
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
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Mac Software
AppleInsider › Forums › Software › Mac Software › Excel: multiplying money with time