Excel: multiplying money with time
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...
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
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.
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