Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-26-2011, 02:11 PM
IndCom IndCom is offline Timesheet formula Windows XP Timesheet formula Office 97
Novice
Timesheet formula
 
Join Date: May 2011
Posts: 8
IndCom is on a distinguished road
Default Timesheet formula

New here and I hope you folks can help. Because I've frazzled my brains trying to figure this one out.

Could someone please provide me with the following formulas?

This is for a daily calculation - no break columns are used.

MONDAY
Col A5 Time Start in Time Format 10:00 PM
Col B5 Time Stop in Time Format 8:00 AM

And here's where I need the formulas:

Col C5 Total Regular Hours in Numerical Value example ( 8.0 )
Col D5 Total Overtime Hours in Numerical Value example ( 2.0 )



Much help needed and many thanks for your considerations!
Reply With Quote
  #2  
Old 05-26-2011, 02:21 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Timesheet formula Windows 7 32bit Timesheet formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Welcome to the forum.

Please can you define what regular hours and overtime hours are?
Reply With Quote
  #3  
Old 05-26-2011, 02:29 PM
IndCom IndCom is offline Timesheet formula Windows XP Timesheet formula Office 97
Novice
Timesheet formula
 
Join Date: May 2011
Posts: 8
IndCom is on a distinguished road
Default

Hope this helps:

All calculations are on Row 5 for that day, Monday
The next days hours will follow are on the next Rows as needed.

"A" Column Time Start in Military Time Format (18:00)
"B" Column Time Stop in Military Time Format (08:00)

What's need to calculate from Columns "A" & "B".

"C" Column Total Regular Hours worked in NUMERICAL Value (8.0)
"D" Column Total Overtime Hours (if any over Regular Hours) in NUMERICAL Value (2.0)

Have I confused everyone yet?!
Reply With Quote
  #4  
Old 05-26-2011, 02:34 PM
IndCom IndCom is offline Timesheet formula Windows XP Timesheet formula Office 97
Novice
Timesheet formula
 
Join Date: May 2011
Posts: 8
IndCom is on a distinguished road
Default

Bear in mind that somedays are 12 hour shift days that are REGULAR WORKED HOURS.

Basically, in my case

Saturday & Sunday= 12 hour shifts & are REGULAR HOURS
Thursday & Fridays= 8 hour shifts and are REGULAR HOURS
This makes a 40 hour workweek of REGULAR HOURS.

Anything over this 40 hours is considered OVERTIME HOURS. But, I'm trying to break it down into a daily log basis, or have it show as "0.0" for any OVERTIME that day.


Now I know I've confused the issue.
Reply With Quote
  #5  
Old 05-26-2011, 02:42 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Timesheet formula Windows 7 32bit Timesheet formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

In C5
Code:
=B5+(A5>B5)-A5-D5
In D5
Code:
=MAX(0,B5-A5+(A5>B5)-1/3)

EDIT: This is a solution to the information in post #3. It does not account for differing overtime hours on weekends; it assumes 8 hour shifts.
Reply With Quote
  #6  
Old 05-26-2011, 02:51 PM
IndCom IndCom is offline Timesheet formula Windows XP Timesheet formula Office 97
Novice
Timesheet formula
 
Join Date: May 2011
Posts: 8
IndCom is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
In C5
Code:
=B5+(A5>B5)-A5-D5
Gave a value of -1.67 in both the 12hr and 8hr shifts

In D5
Code:
=MAX(0,B5-A5+(A5>B5)-1/3)
Gave a value of 0.17 in the 12hr shift, but 0.0 in the 8hr shift


EDIT: This is a solution to the information in post #3. It does not account for differing overtime hours on weekends; it assumes 8 hour shifts.
Think I've tried that one before. Any other help and ideas are appreciated!
Reply With Quote
  #7  
Old 05-27-2011, 12:40 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Timesheet formula Windows 7 32bit Timesheet formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

For Excel to be able to distinguish weekdays and weekends, you will need to put the date and time into the cells instead of just the time. If that is acceptable then I can give you a formula to do it.
Reply With Quote
  #8  
Old 05-27-2011, 02:00 AM
IndCom IndCom is offline Timesheet formula Windows XP Timesheet formula Office 97
Novice
Timesheet formula
 
Join Date: May 2011
Posts: 8
IndCom is on a distinguished road
Default

Let me try it out.

So, I just format the cells A5 & B5 separately for date and time......right?

This'll be something new for me to try .
Reply With Quote
  #9  
Old 05-27-2011, 03:05 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Timesheet formula Windows 7 32bit Timesheet formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

For example, you would type 26/05/2011 10:00PM into one cell and 27/05/2011 8:00AM into the other.
Reply With Quote
  #10  
Old 05-27-2011, 06:11 AM
IndCom IndCom is offline Timesheet formula Windows XP Timesheet formula Office 97
Novice
Timesheet formula
 
Join Date: May 2011
Posts: 8
IndCom is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
For example, you would type 26/05/2011 10:00PM into one cell and 27/05/2011 8:00AM into the other.
Okay. got 'em formated M/D/Y 00:00PM

Example: 3/9/11 08:00PM

Should I format this with military time (24 hour)- - - or will standard time do the trick? Military time format with date will read M/D/Y 00:00 -

Example 3/9/11 20:00


OK. Lay it on me!


Again......many many thanks for the help here.
Reply With Quote
  #11  
Old 05-27-2011, 07:08 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Timesheet formula Windows 7 32bit Timesheet formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

It doesn't matter how you format the cell provided that the underlying piece of data is a date and time.

These formulas assume that if a shift starts on a Saturday or Sunday then a standard shift will be 12 hours. If the shift starts on any other day then a standard shift will be 8 hours. They also assume that Start Time < End Time.


In C2, with the cell formatted as [hh]:mm
Code:
=MIN(B2-A2,IF(OR(WEEKDAY(A2)={1,7}),1/2,1/3))
In D2, with the cell formatted as [hh]:mm
Code:
=B2-A2-C2
Attached Files
File Type: xls Example.xls (15.0 KB, 17 views)
Reply With Quote
  #12  
Old 05-27-2011, 11:23 AM
IndCom IndCom is offline Timesheet formula Windows XP Timesheet formula Office 97
Novice
Timesheet formula
 
Join Date: May 2011
Posts: 8
IndCom is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
It doesn't matter how you format the cell provided that the underlying piece of data is a date and time.

These formulas assume that if a shift starts on a Saturday or Sunday then a standard shift will be 12 hours. If the shift starts on any other day then a standard shift will be 8 hours. They also assume that Start Time < End Time.


In C2, with the cell formatted as [hh]:mm
Code:
=MIN(B2-A2,IF(OR(WEEKDAY(A2)={1,7}),1/2,1/3))
In D2, with the cell formatted as [hh]:mm
Code:
=B2-A2-C2
Yes. The begining of the work week starts on Saturday and ends on Friday.

Okay. Got most of it working, but still having some problems getting the totals for each week to read in a numerical total value plus the Total Hours for both weeks. I think part of the problems are the shaded areas with no date/time (for they are my regular days off).

I attached the sheet that I'm working with to help clarify things a mite.

Thanks so much for the help you've provided for so far!
Attached Files
File Type: xls Time Formula.xls (22.0 KB, 12 views)
Reply With Quote
  #13  
Old 05-27-2011, 01:27 PM
IndCom IndCom is offline Timesheet formula Windows XP Timesheet formula Office 97
Novice
Timesheet formula
 
Join Date: May 2011
Posts: 8
IndCom is on a distinguished road
Default

HEY! I GOT IT!

Dummy me forgot to format the "Total Cells" for [hh]:mm.


Works like a charm now! Many, many thanks for all you've done!

Have another look and see what you think.
Attached Files
File Type: xls Time Formula.xls (22.0 KB, 15 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Timesheet formula PMT Formula OneOleGuy Excel 4 03-05-2011 09:54 AM
Timesheet formula Help with Formula Corca Excel 6 02-22-2010 09:40 PM
Timesheet formula Timesheet formula help beardking Excel 2 11-10-2009 02:06 AM
Timesheet formula If formula sixhobbits Excel 1 10-02-2009 08:02 AM
Help for formula dehann Excel 5 05-01-2009 10:44 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:22 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft