Jump to content


Photo

Weekly Time Calculation

format painter custom format PDF IF times

  • Please log in to reply
10 replies to this topic

#1 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 22 February 2017 - 11:41 PM

I'm entering formulas into a timesheet.  Rows 8 thru 14 are for the days of the week.  Column I reflects the total hours for each row.  Column J reflects the regular hours for each row.  Column O reflects overtime for each row in excess of 8 hours regular time reflected in Column J.

 

I need a formula on Friday's and Saturday's row that will reflect only regular hours not to exceed 40 during the week.  In rows 8 thru 12, Column J, I have the formula =FLOOR(MIN($I8,8/24),15/24/60), and in Column O I have the formula =IF(I8<=J8,0,FLOOR(I8-J8+0.0000001,15/24/60)).  This works well for each day until Friday.  If an employee works Sunday thru Thursday, 8 regular hours a day, then any time worked Friday and Saturday wouldn't be reflected in Column J (regular hours), but should be in Column O (overtime).  On the other hand, if an employee works regular hours only Monday thru Friday, I need Friday's row to include the last 8 hours (of a total of 40) in Column J.

 

I don't know how to modify my formula to produce this result.  Can anyone help?



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 23 February 2017 - 01:51 PM

Hi Zella,

 

I'm having some difficulty understanding exactly what you're doing. If you could attach an example it would really help (instructions here).

 

That said, I have tried to put together my own example based on your description.

 

From what I understand, the rules are as follows:

  • If an employee works more than 8 hours in one day, the excess hours are counted as overtime.
  • If an employee works more than 40 hours in one week, any hours above 40 are counted as overtime.

I have modeled this by creating a running total of hours that can be used to check if the employee has exceeded 40 hours. I then used the following formula to get hours above 40:

=IF(D8>40,D8-40-SUM($E$2:$E7),0)

 

The only novel thing about this formula is the SUM function using a relative reference. This automatically returns a SUM of every row above the current row, allowing you to subtract the earlier values from this one.

You can see more about absolute and relative references in the video lesson: Essential Skills Lesson 3-12 Understand absolute and relative cell references.

 

Now that you have this information, you can use a simple IF function to get a total number of overtime hours:

=IF(D8>40,E8,B8-C8)

 

The IF Function is fully explained in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

The IF function lesson actually covers a very similar scenario of calculating overtime hours, so I'd highly recommend watching it if you haven't already.

 

I have attached a copy of my example workbook:

 

Attached File  Overtime.xlsx   10.32KB   27 downloads

overtime.png

 

I hope this is useful to you, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 23 February 2017 - 02:26 PM

Thank you ever so much for your reply.  I thought I had attached the spreadsheet.  The depiction you provide reflects 8 hours for Saturday and Sunday that shouldn't be there. Working 8 hours Monday through Friday gives the employee the 40 regular hours authorized.  The hours worked on the weekend should only be reflected in Column E.  I'll attempt, again, to attach my timesheet.  I've made a couple notes on it to help explain what I'm attempting to do with formulas.

Attached Files



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 23 February 2017 - 04:22 PM

Hi Zella,

 

I have taken a look at your workbook and I think I have the solution you need. You can use an IF function to exclude the weekends from 'Regular pay hours' and can then use the same running totals technique shown above to extract hours above 40.

 

I have also simplified some of the formulas by removing the FLOOR and division steps and placing the comparison values in cells X1 and X2. There was nothing wrong with your original formulas, I just found it easier to understand the calculations this way.

 

I notice that you're working extensively with date and time values, so you (or anyone working on a similar system) might like to take a look at the video lesson: Expert Skills Lesson 3-7 Understand Excel date serial numbers and custom date formats.

 

I have attached my revised copy:

 

Attached File  Test Timesheet.xlsx   16.14KB   25 downloads

overtime2.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 25 February 2017 - 11:04 PM

I don't think my problem is yet understood.

 

This same timesheet is used by everyone.  Some people work Sunday thru Thursday, others Monday thru Friday, others every day of the week.  For each employee, no matter what day they work or how many hours in a single day they work, anything in excess of 8 hours a day is overtime and anything in excess of 40 hours a week is overtime.  So one employee may reach their 40 regular hours a week Sunday thru Thursday, while other won't reach 40 hours a week until Friday, while yet others may work Tuesday thru Saturday.  I need to create a formula for each day that adds the hours for that day, putting up to 8 in the regular time column and whatever is left over in the overtime column.  Once the daily total reaches 40 for that week, the daily row needs to enter all time in the overtime column.  If someone works 5 hours Sunday, 8 hours each Monday thru Friday, and 4 hours on Saturday ...

 

5 hours Sunday plus 8 hours Monday, Tuesday, Wednesday, Thursday are all regular hours, but only 3 of Friday's hours are regular and the other 5 are overtime.  Plus anytime that person works on Saturday is all overtime.

 

If another person works 8 hours per day Monday thru Friday only, all hours are reflected as regular time.  If that same person also works 4 hours Saturday, then all 4 hours would be overtime (in excess of 40 that week).

 

I've tried several "IF" formulas but to no success.  I've included some notes on the attached to help clarify my problem.Attached File  Test Timesheet.xlsx   17.5KB   21 downloads



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 27 February 2017 - 01:01 PM

Hi Zella,

 

I think I have what you need now. Exactly the same formulas as shown above can be used to achieve what you need with a few minor adjustments:

 

Attached File  Test Timesheet 2.xlsx   15.47KB   22 downloads

timesheet2.png

 

I have highlighted the appropriate totals in yellow - I think they now show the numbers you need.

 

I have added a few new columns to calculate the correct results. You could hide these if you don't want them to be visible to the user.

 

Hiding columns is explained in the video lesson: Expert Skills Lesson 7-7 Hide and unhide worksheets, columns and rows.

 

I hope this is what you needed, but please feel free to reply if you have any more questions about this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#7 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 28 February 2017 - 04:36 PM

I know enough about Excel to be dangerous and I'm having a hard time adapting your formulas to the actual timesheet I'm working with.  A can't modify the timesheet layout (even though I know how to hide rows and columns).  I'm hoping you'll have patience with me one more time and look at the attached document.  I've typed in scenarios for what each formula should do.  The non-highlighted cells are working fine with the formulas I have in them, I only need help with those few highlighted. Will you attempt one more time to help me? :huh:

Attached Files



#8 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 01 March 2017 - 12:11 PM

Hi Zella,

 

From what I can gather, the only problem with my solution above is that I have added new columns to calculate the result. It's unusual that you wouldn't be able to do this, but the problem is still solvable by combining the formulas together to create one (very complicated) formula:

=IF(SUM($G$5:$G6)+MIN($S7,EightHours)>FortyHours,ROUND(FortyHours-SUM($G$5:$G6),1),MIN($S7,EightHours))

 

The formula works as follows:

If the sum of the previous hours plus today's hours would be greater than 40 hours:

Calculate 40 hours minus the sum of the previous hours, rounded to 1 decimal place (to prevent results like 0.00000000001).

Otherwise, return either today's hours or 8 hours (whichever is lower).

 

This does exactly the same thing as the formulas in the previous examples. I usually try to avoid this kind of approach as it makes formulas harder to understand.

 

You'll notice that I have replaced the complicated system of calculating the values for 8 hours and 40 hours with named constants called EightHours and FortyHours. This allows me to make the formulas easier to read without needing to add extra data on the worksheet itself.

 

You can see how to create and work with named constants in the video lesson: Expert Skills Lesson 4-2 Manually create single cell range names and named constants.

 

I have attached a copy of my workbook with the formulas applied:

 

Attached File  Test Timesheet for Post.xlsx   15.53KB   26 downloads

testtimesheet.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#9 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 01 March 2017 - 02:18 PM

This seems to work perfectly.  Thank you!  There is one small detail however.  If there is no clock time reflected in a given row, the cells in Column K should be blank, not reflect 00:00.  Can this be fixed in the formulas in Column K?

 

Actually, I tried to copy your formulas into the actual timesheet (that included all the headings, signature stamps, etc.) and I got an error of ##### in your formula cells.  I couldn't figure out what the problem was, so I ended up recreating all the headings, etc. in the sheet you sent back.

 

I really do appreciate you continuing to work with me on this.  Even my original formulas were provided by someone else (the ones with FLOOR in them).  I've taken the Smart Method lessons for Excel 2007 and am now taking them for Excel 2013, but I simply can't get the detailed formulas I need from those generic videos.  Thanks again.

 

One other question, if, once a timesheet is filled out completely, can it be converted to a PDF without damaging the data reflected in the formula cells?



#10 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 02 March 2017 - 10:34 AM

Hi Zella,

 

As you can see, I've applied a custom format to column G that causes zeroes to appear as blanks. To replicate this in column K, all you need to do is copy the same format to it.

 

You could do this by using the format painter, as shown in: Essential Skills Lesson 4-22 Use the Format Painter.

You could also do this by applying the custom format manually, as shown in: Essential Skills Lesson 4-4 Create custom number formats.

 

Copying the formulas into another sheet probably did work, but Excel often tries to readjust formulas if they are pasted into a different location, so this is probably why you found errors when you copied the formulas. It should be possible to make this work, but you'd need to carefully readjust the formulas to make sure they are pointing to the correct cells on the destination sheet.

 

Finally, yes, you can save a workbook as a PDF without losing any data. You can see how to save a workbook as a PDF in the video lesson: Essential Skills Lesson 1-7 Understand common file formats.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#11 Zella

Zella

    Member

  • Members
  • PipPip
  • 12 posts

Posted 04 March 2017 - 06:46 PM

I am working my way through the Smart Method videos and find them very informative and helpful.  I've got my timesheet polished up now, with your help.  I am so grateful for your assistance, guidance, knowledge, and patience in following through with me to a successful product!  Thank you ever so much!  Have a blessed day. :D







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users