On excel, how can i get a formula to my working time. Let say my time in is 6:15pm to 2:15am because im working in night shift.any suggestion? Thanx

Use a DATE & TIME combination, not just TIME, that will make the calculations simpler. MIKE

I did but the answer is not the right one, its 16hrs. Thanx

In cell A1 put the date & time: 12/01/10 6:15 pm

In cell B1 put the date & time: 12/02/10 2:15 amIn cell C1 enter the formula: =B1-A1

You will probably see the answer as .333333To get the hours you want, you will need to Custom Format cell C1

With your cursor on cell C1

Right Click

Select Format Cells

Select Custom (bottom of the list)

Scroll down the list until you find the custom format of: [h]:mm:ss

It must be the format with the square brackets......

Select the format, click OK

It should now look like:A B C 1) 12/1/10 6:15 PM 12/2/10 2:15 AM 8:00:00MIKE

I tried and its work but how can i hide the date,it is possible that only time i want to put in the cell? because it seems that i always edit the date.

Thank you for your help

If you really must use just TIME then try this: =IF(A1>B1,B1+1-A1,B1-A1)

Notice that the formula has a +1, this causes Excel to treat B1 as if it were in the next day.

MIKE

Thank you mike, theres another thinngs, in dayshift my time in is 9am to 6pm but i have a 1hr breake time and also in nightshift i have 30mins breaktime,how can i get the exact 8hrs working time? Because when i sum up all my working time my breaktime well included.

It is possible to make a formula for nightpremium working hours?for example i work from 5:30pm to 2am, and from 10pm to 2am is the night premium working hours,how can i get the numbers of night premium hours in my night shift schedule?

Thank you

What version of Excel are you using? Have you taken the time to check out the templates that come with your Excel?

Simply do a File, New, and you should be offered a number of templates that may suit your needs, also do a web search for Excel timecard or work schedules. There are a large number of freeware templates available from MS and others.

I would check these resources first before you embark on creating a timesheet from scratch

I'm not one to reinvent the wheel, as it were.

in dayshift my time in is 9am to 6pm but i have a 1hr breake timealso in nightshift i have 30mins breaktimeJust subtract your break time from your total time.

It is possible to make a formula for nightpremium working hours?Yes it's possible, but I would advise to first try finding a template that's already built or one that is close to what your needs are and we can modify that if necessary. It will also give you some ideas of how you might want to build yours.

MIKE

Mike im using excel 2007 version, i already check those templates but its complicated, i want to make simple and easy to understand. Actually, im making a payslip or payroll in excel, which i think its a very convient way.I already started to make one but i manually put the numbers of hours work and the nightpremium worked. I want to make it simple, like i want to put only in the cell the time in and time out and it automatic calculate the number of hours ,the number of overtime ,and the night premium.

thank you

I already started to make one but i manually put the numbers of hours work and the nightpremium worked.Post an example of your spreasheet with column headings and row numbers, but read this first:

This is an expanded version of DerbyDad03 How To: Posting Tables and VBA Code With The Pre Tag

Step 1. Click the Pre icon found above the Reply box.

Step 2. Enter your data/table(s) or VBA code between the tags. (See below)

Step 3. Click thePreview FollowUp button, below on the right,DO NOTpress Submit Follow Up.

Step 4. If you need to fix the layout, the top window shows what your messages looks like, the bottom window allows you to fix and modify your message. Make your changes here.

Step 5. At the top there is a box with "Check To Show Confirmation Page Again" check this box.

Step 6. Click either Confirm button to Preview the post again.You can now edit your message again, make your changes/edits and

to review these new edits start at step 5.

You can keep making corrections until you get it the way you want.

Pre Tags

When you click on the pre button,

two symbols should appear in your message,

they should be the word PRE

surrounded by left & right arrows

they should look like:< pre > < / pre >

I've added spaces between everything to highlight what they look like, they will not have the spaces normally.

Post your data between the two symbols:

< pre >

A B C 1) Data Goes here 2) more data here

< / pre >This will keep the columns aligned.

When you preview your message, you can now modify the columns so everything lines up correctlyMIKE

A B C D E F G

1. 8 2 4 $1 $1.4 $1.2 salaryunder column A is there regular hour which 8hrs, under B is there overtime, under C is the night premium hours, under D is there regular rate, under E is there overtime rate, under F is there night premium rate and under G is there salary that day which i put a formula, goes like this: =(A1*D1)+(B1*E1)+(C1*F1).

i manually put the numbers of hours each cell, from regular time to night premium,

i want to make a new one that i only put the time in and time out and it automatically put a numbers of hours to there designated cell.

please help me.

thank you

im sorry i forget to use the pre buttom to aligned A B C D E F G 1. 8 2 4 $1 $1.4 $1.2 salaryi hope this could be aligned

I want to make it simpleMaking it simple for the user, usually means it's complicated to program.

dayshift my time in is 9am to 6pmDo you have other shifts? What are there times.

i work from 5:30pm to 2am,

Is that a regular shift?

Do you only work regular shift hours or can you work a partial shift?MIKE

I work two shift, 9am to 6pm dayshift and the nightshift is from 5:30pm to 6pm, every week i changed shift.

nightshift is from 5:30pm to 6pmI doubt you work only a half hour, I presume it

should be 5:30 PM until 2:00 AM ? With a half hour break.

and from 10pm to 2am is the night premium working hours

So every other week you work 4 hours regular hours and 4 hours premium hours?

Because you take your break prior to 10:00 PM.MIKE

Im sorry, its 5:30pm to 2am with 30mins breaktime, in night shift. Yes i work every other week 4hrs regular time and 4hrs night premium.

You have three pay rates, how are they calculated, IE Your Regular Hourly Rate is $12.00

Your Overtime Rate is Time & Half, $12.00 + $6.00 = $18.00Your Prime Rate is ?

Or do you use just dollar amounts?

MIKE

im sorry, prime rate means night premium? its = C1*F1 its not the right figure that i post, i just want to show you my sample computation that's why im using dollar amount so you would know its the number of hours or the rate.

I've made this very simple, there's not a lot of error checking going on.

Others may chime in with additional ideas and improvements, so we'll take it in steps.I've made several assumptions to make life easy.

Your Day shift start time must always be 9:00 AM

Your Night shift start time must always be 5:30 PM

I've used those times to automatically deduct your lunch hour or break half hour from your Total Hours to arrive at a Hours Worked figureA B C D E F G H I 1) Reg Rate OT Rate Prime Rate 2) $12.00 $18.00 $20.00 3) Date Start Time End Time Total Hours Hours Worked Reg Hours Overtime Prime TOTALS 4) 12/4/2010 9:00:00 AM 6:00:00 PM 9 8 8 0 0 $96.00 5) 12/5/2010 5:30:00 PM 2:00:00 AM 8.5 8 4 0 4 $128.00 6) 12/6/2010 9:00:00 AM 7:00:00 PM 10 9 8 1 0 $114.00The first formula Total Hours, Column D cell 4

=(C4-B4+(C4<B4))*24

the cell should be formated as General or Numbers.The second formula Hours Worked, Column E cell 4

=IF(B4=0.375,((C4-B4+(C4<B4))*24-1),((C4-B4+(C4<B4))*24-0.5))

the cell should be formatted as General or Numbers.

See if you can get those two working correctly.

MIKE

hi, 1.i tried what the formula but on E5 cell its not 8 hours worked like what you post but its -16 hours worked.

2. on cell H4 how you get the answer?whats the formula on the night prime?

3. also in cell G6 how you get the answer? whats the formula on the overtime?thanks

Ask Your Question

Weekly Poll