Articles

Excel Time Calculations

December 1, 2010 at 12:21:04
Specs: Windows 7, Core 2

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

See More: Excel Time Calculations

Report •


#1
December 1, 2010 at 12:26:13

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

MIKE

http://www.skeptic.com/


Report •

#2
December 1, 2010 at 13:14:17

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

Report •

#3
December 1, 2010 at 14:00:41

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 am

In cell C1 enter the formula: =B1-A1
You will probably see the answer as .333333

To 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:00

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
December 2, 2010 at 04:54:39

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

Report •

#5
December 2, 2010 at 05:55:34

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

http://www.skeptic.com/


Report •

#6
December 2, 2010 at 09:35:16

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

Report •

#7
December 2, 2010 at 10:40:37

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 time
also in nightshift i have 30mins breaktime

Just 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

http://www.skeptic.com/


Report •

#8
December 2, 2010 at 16:57:02

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

Report •

#9
December 2, 2010 at 17:56:39

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 the Preview Follow Up button, below on the right, DO NOT press 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 correctly

MIKE

http://www.skeptic.com/


Report •

#10
December 3, 2010 at 17:46:14

A B C D E F G
1. 8 2 4 $1 $1.4 $1.2 salary

under 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


Report •

#11
December 3, 2010 at 17:50:16

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     salary

i hope this could be aligned


Report •

#12
December 3, 2010 at 21:45:51

I want to make it simple

Making it simple for the user, usually means it's complicated to program.

dayshift my time in is 9am to 6pm

Do 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

http://www.skeptic.com/


Report •

#13
December 4, 2010 at 07:55:19

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

Report •

#14
December 4, 2010 at 10:02:27

nightshift is from 5:30pm to 6pm

I 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

http://www.skeptic.com/


Report •

#15
December 4, 2010 at 10:36:59

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.

Report •

#16
December 4, 2010 at 12:05:28

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

Your Prime Rate is ?

Or do you use just dollar amounts?

MIKE

http://www.skeptic.com/


Report •

#17
December 5, 2010 at 18:03:54

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.


Report •

#18
December 5, 2010 at 18:38:35

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 figure

        A          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.00

The 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

http://www.skeptic.com/


Report •

#19
January 3, 2011 at 20:40:43

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


Report •


Ask Question