# Time convertion

#### jordan11221992

##### New Member
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Zot

##### Well-known Member
You need to convert hour, min, sec into number.

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

#### DSCfromCFA

##### Board Regular
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

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
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!

#### Dave Patton

##### Well-known Member
VBA Examples 2020.xlsm
ABCDEF
1DateinoutRate15.35
21-Jan-2107:0018:0015.35168.85
310-Jan-2109:0017:0015.35122.80
411-Jan-2112:0017:0015.3576.75
5368.40
4a
Cell Formulas
RangeFormula
E1:E4E1=15.35
F2:F4F2=(C2-B2)*24*\$E\$1
F5F5=SUM(F2:F4)

Replies
3
Views
90
Replies
0
Views
131
Replies
1
Views
115
Replies
0
Views
132
Replies
0
Views
165

1,127,100
Messages
5,622,724
Members
415,923
Latest member
Kam80

### 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.

### Which adblocker are you using?

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

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