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!

Welcome to the forum.

Please can you define what regular hours and overtime hours are?

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

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.

In C5
In D5

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!

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.

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 .

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.

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
In D2, with the cell formatted as [hh]:mm

Attached Files

Example.xls (15.0 KB, 19 views)

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

Time Formula.xls (22.0 KB, 14 views)

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

Time Formula.xls (22.0 KB, 17 views)