Time convertion

jordan11221992

New Member
Joined
Jan 2, 2021
Messages
22
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
On sheet 1 i ha e my schedule monday-sunday with everyones in and out times for each day of the week. On sheet 2 i have the same template but for example sheet 1, c12 is in time d12 is out time. On sheet 2 i have b2='sheet1'!d12-'sheet1'!c12 to give me the total hours for the day sheet 1 says 7:00 a.m. in c12 and 6:00 p.m. in d12. On sheet 2 in b4 it is ='sheet1'!d12-'sheet1'!c12. It gives me 11:00 cool yep 11 hours indeed.
Now on sheet 2 i need to multiply the hours by their wages. So on sheet 2 i have =sum(b4*$j$4). J4 is their wage. So it ends up trying to multiply 11:00×$15.35 amd gives me $7.04 instead of what it actually is $168.85. How do i fix this?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
You need to convert hour, min, sec into number.

For:
Hour (B4 * 24)
Min (B4 * 1400)
Sec (B4 * 86400)
 
Upvote 0
G'day Jordan,

I don't have the solution, but the problem is caused by how time is handled. The 11:00 you are getting is being seen by the time formatting of the cell as 11:00am, not 11 elapsed hours. 11:00am is just less than half a day so that is why you are getting the result of just under half the hourly rate as your answer.

The solution of simply changing the cell format to numeric rather than time/date is that you than have the issue of the hour clicking over a .59, not .99. To overcome that you need to convert times to decimal, (for example so that the half hour is .50 not .30). It gets messy, but is certainly not impossible - my own time sheet converts hours:minutes to decimal times for the calculations, although I'm not sure of just where they do it.

My apologies if you already knew this stuff, and hopefully someone who knows a lot more than me will be along shortly to assist you.

Cheers

shane
 
Upvote 0
Additional reading on how Excel store time and date


 
Upvote 0
What about

Book1
ABCDEFG
1inout
2Fri, Jan-01-217:0018:00$ 15.35168.85168.85
3Sat, Jan-02-218:0019:00$ 15.35168.85168.85
4Sun, Jan-03-219:0020:00$ 15.35168.85168.85
5Mon, Jan-04-2110:0021:00$ 15.35168.85168.85
6Tue, Jan-05-2111:0022:00$ 15.35168.85168.85
7Wed, Jan-06-2112:0023:00$ 15.35168.85168.85
8Thu, Jan-07-2113:000:00$ 15.35168.85168.85
9Fri, Jan-08-2114:001:00$ 15.35168.85168.85
10Sat, Jan-09-2115:002:00$ 15.35168.85168.85
11Sun, Jan-10-2116:003:00$ 15.35168.85168.85
12Mon, Jan-11-2117:004:00$ 15.35168.85168.85
Sheet1
Cell Formulas
RangeFormula
E2:E12E2=15.35
F2:F12F2=TEXT(((C2-B2)*24)*E2,"###.#####")
G2:G12G2=((C2-B2)*24)*E2
 
Upvote 0
Second row f2:f12 worked and put the dollsr amount in each persons day. Now it wont let me add the column for total amount the the day with all the other employees. So i only have two people on monday one with 54. And the other with 60. Everyone else is off so i left them blank on my schedule. When i try to add the total for the day at the bottom =b3:b9 it says #spill!
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top