Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of hours
I run a small business that I need to schedule the work hours for my
employees on a sheet. A particular cell contains the hours work for an mployee from 2 am-5 Pm, 2 pm- 9pm. That were exactly what I put down. However, I want excel to automatically add the total hours worked by each employee in the right most colum. However, I cannot use the sum function with the format from above. Is there a different way for me to input the hours, so that the sum function will work, but I still want a format similar to the once from above? Thank You. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of hours
Hi Raymond
Have a look at Number formats, Hour Category you will find precisely the number format you are looking for. Please note you must input hours as 00:00:00 (AM or PM mention will be automatically added) HTH Cordially Pascal "Raymond" a écrit dans le message de ... I run a small business that I need to schedule the work hours for my employees on a sheet. A particular cell contains the hours work for an mployee from 2 am-5 Pm, 2 pm- 9pm. That were exactly what I put down. However, I want excel to automatically add the total hours worked by each employee in the right most colum. However, I cannot use the sum function with the format from above. Is there a different way for me to input the hours, so that the sum function will work, but I still want a format similar to the once from above? Thank You. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of hours
THANK YOU PAPOU. HOWEVER, WHAT IF I WANT TO HAVE A COUPLE OF DAYS OFF FOR
AN EMPLOYEE BY PUTTING THE WORD OFF THERE? IT WOULD NOT ADD UP TO A RIGHT NUMBER, IS IT? "Papou" wrote in message ... Hi Raymond Have a look at Number formats, Hour Category you will find precisely the number format you are looking for. Please note you must input hours as 00:00:00 (AM or PM mention will be automatically added) HTH Cordially Pascal "Raymond" a écrit dans le message de ... I run a small business that I need to schedule the work hours for my employees on a sheet. A particular cell contains the hours work for an mployee from 2 am-5 Pm, 2 pm- 9pm. That were exactly what I put down. However, I want excel to automatically add the total hours worked by each employee in the right most colum. However, I cannot use the sum function with the format from above. Is there a different way for me to input the hours, so that the sum function will work, but I still want a format similar to the once from above? Thank You. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of hours
This is a little convoluded but here goes:
if cell a3 is '2am-5pm =SEARCH("-",A3,1) is put in cell b3 =LEFT(A3,B3-1) is put in c3 =RIGHT(A3,LEN(A3)-B3) is put in c3 =IF(RIGHT(C3,2)="am",VALUE(LEFT(C3,LEN(C3)-2)&" am"),VALUE (LEFT(C3,LEN(C3)-2)&" pm")) in e3 give you the start time =IF(RIGHT(D3,2)="am",VALUE(LEFT(D3,LEN(D3)-2)&" am"),VALUE (LEFT(D3,LEN(D3)-2)&" pm")) in f3 give you the end time =IF(F3E3,(F3-E3)*24,(F3+1-E3)*24) in g3 give you the hours difference (assuming that start time and end time are on same day or successive days) John -----Original Message----- THANK YOU PAPOU. HOWEVER, WHAT IF I WANT TO HAVE A COUPLE OF DAYS OFF FOR AN EMPLOYEE BY PUTTING THE WORD OFF THERE? IT WOULD NOT ADD UP TO A RIGHT NUMBER, IS IT? "Papou" wrote in message ... Hi Raymond Have a look at Number formats, Hour Category you will find precisely the number format you are looking for. Please note you must input hours as 00:00:00 (AM or PM mention will be automatically added) HTH Cordially Pascal "Raymond" a écrit dans le message de ... I run a small business that I need to schedule the work hours for my employees on a sheet. A particular cell contains the hours work for an mployee from 2 am-5 Pm, 2 pm- 9pm. That were exactly what I put down. However, I want excel to automatically add the total hours worked by each employee in the right most colum. However, I cannot use the sum function with the format from above. Is there a different way for me to input the hours, so that the sum function will work, but I still want a format similar to the once from above? Thank You. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of hours
I should add it would be far simpler to put the start time in one column and end time in another (formatted to show hh:mm) and then just calculate the difference....if you want the option to put in "off" you could just start with if (cell="off",0,normal calculation). John -----Original Message----- This is a little convoluded but here goes: if cell a3 is '2am-5pm =SEARCH("-",A3,1) is put in cell b3 =LEFT(A3,B3-1) is put in c3 =RIGHT(A3,LEN(A3)-B3) is put in c3 =IF(RIGHT(C3,2)="am",VALUE(LEFT(C3,LEN(C3)-2)&" am"),VALUE (LEFT(C3,LEN(C3)-2)&" pm")) in e3 give you the start time =IF(RIGHT(D3,2)="am",VALUE(LEFT(D3,LEN(D3)-2)&" am"),VALUE (LEFT(D3,LEN(D3)-2)&" pm")) in f3 give you the end time =IF(F3E3,(F3-E3)*24,(F3+1-E3)*24) in g3 give you the hours difference (assuming that start time and end time are on same day or successive days) John -----Original Message----- THANK YOU PAPOU. HOWEVER, WHAT IF I WANT TO HAVE A COUPLE OF DAYS OFF FOR AN EMPLOYEE BY PUTTING THE WORD OFF THERE? IT WOULD NOT ADD UP TO A RIGHT NUMBER, IS IT? "Papou" wrote in message ... Hi Raymond Have a look at Number formats, Hour Category you will find precisely the number format you are looking for. Please note you must input hours as 00:00:00 (AM or PM mention will be automatically added) HTH Cordially Pascal "Raymond" a écrit dans le message de ... I run a small business that I need to schedule the work hours for my employees on a sheet. A particular cell contains the hours work for an mployee from 2 am-5 Pm, 2 pm- 9pm. That were exactly what I put down. However, I want excel to automatically add the total hours worked by each employee in the right most colum. However, I cannot use the sum function with the format from above. Is there a different way for me to input the hours, so that the sum function will work, but I still want a format similar to the once from above? Thank You. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif and translate annual hours into weekly quarter hours | Excel Worksheet Functions | |||
wages - multiply hours and minutes by number of hours worked | Excel Discussion (Misc queries) | |||
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
how to add hours and show actual hours not decimal numbers | New Users to Excel | |||
Convert hours and minutes in time format into fractions of hours.. | Excel Worksheet Functions |