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?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,000
Office Version
  1. 2016
Platform
  1. Windows
You need to convert hour, min, sec into number.

For:
Hour (B4 * 24)
Min (B4 * 1400)
Sec (B4 * 86400)
 

DSCfromCFA

Board Regular
Joined
Feb 27, 2018
Messages
147
Office Version
  1. 365
Platform
  1. Windows
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
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

jordan11221992

New Member
Joined
Jan 2, 2021
Messages
22
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,460
Messages
5,624,863
Members
416,063
Latest member
chaulon199

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
Top