+ Reply to Thread
Results 1 to 14 of 14

Timesheet forumla

  1. #1
    Registered User
    Join Date
    04-23-2014
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Timesheet forumla

    So I have a simple Project Timesheet that I wish to use. I have found that if I use the format cells custom feature and enter 0\:00 I can input just the numbers and it will express them with the colon without having to type it. I also found an example where using a hidden column you should be able to convert this to time so that in the number of hours worked column it will show correctly as hours and not decimals. (ie 7:45 hours and not 7:75). The formula that the person had input and in their spreadsheet it worked was =TIME(TRUNC(B4/100),MOD(B4,100),0) I have managed to get this to work if I put the cell format to h:mm.
    I am still having problems getting a formula that will give me the number of hours worked. The sample timesheet had columns for a lunch hour that I do not require which was =IF(E4-C4>0,E4-C4-J4,""), and a simple E4-C4 does not work. I have one final column that requires a running total to a final total at the end. I have gone through the forum and the other Timesheets that I have seen have seemed very complicated for what I want to do.

    I am sure there is a simple explanation as to why this will nor work but I can't see it. I am not sure how to put the spreadsheet up here as an example so if someone could let me know that also I would appreciate it, as I realize that it is easier to solve something if you can actually see it. Below is a list of the columns that I am currently working with.

    Columns
    A - Date
    B - Start Time
    C - hidden column with formula to change to time
    D - End Time
    E - hidden column with formula to change to time
    F - Task Description
    G - Daily Hours
    H - Total Hours

    Thanks
    Hils

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Timesheet forumla

    When you say E4-C4 doesn't work, what does that mean? Do you get the wrong result? (if so, give an example) Do you get an error, what sort of error?
    Do people ever work past midnight so that E will be less than C?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Timesheet forumla

    Try using this formula :
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  4. #4
    Registered User
    Join Date
    04-23-2014
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Timesheet forumla

    When I say that E4-C4 does not work I mean that all that appears is 0:00 so it is not calculating anything

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Timesheet forumla

    To upload a spreadsheet (Go Advanced>Manage Attachments)

  6. #6
    Registered User
    Join Date
    04-23-2014
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Timesheet forumla

    Quote Originally Posted by Saarang84 View Post
    Try using this formula :
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    So I tried this but it did not work. my column went to 0:00 again. What should I format these cell to? h:mm or 0\:00 ?
    I tried with both but it did not seem to make any difference

  7. #7
    Registered User
    Join Date
    04-23-2014
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Timesheet forumla

    Quote Originally Posted by ChemistB View Post
    When you say E4-C4 doesn't work, what does that mean? Do you get the wrong result? (if so, give an example) Do you get an error, what sort of error?
    Do people ever work past midnight so that E will be less than C?
    No error code comes up
    People do not normally work past midnight but it has been known to happen.
    I left out lunch times as I have no problem if they enter in their morning hours and then use a separate line to enter in their afternoon hours. This is because they could be working on more than one project at a time.

    Hils

  8. #8
    Registered User
    Join Date
    04-23-2014
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Timesheet forumla

    I am I being blind here as I can not find a attachment button so that I can post a sample of the worksheet

    Hils

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Timesheet forumla

    First, click on "Go Advanced" then scroll down to "Manage Attachments" or click on paperclip on toolbar (it's only there in Advanced)

  10. #10
    Registered User
    Join Date
    04-23-2014
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Timesheet forumla

    Thanks for the help I was obviously not looking hard enough.

    Hopefully here is the spreadsheet I am working on.

    Hils
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Timesheet forumla

    Your column G is formatted to your custom format. You want it to be custom [h]:mm. See attachment
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-23-2014
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Timesheet forumla

    Thank You ChemistB That is working now.

    Last thing to ask is the calculation at the bottom in G24, this is to take the total number of hours and change it to dollars amount. The Daily rate for 8 hours is $1200.00 and therefore the hourly rate is $150.00. I am trying to do this from my end but so far I am not getting the correct amount. I am new to using formulas and so do not always know when certain things have to be converted within the formula to be able to achieve the desired result.

    Hils

  13. #13
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Timesheet forumla

    Taking your time in G23
    =G23 *24 *150

  14. #14
    Registered User
    Join Date
    04-23-2014
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Timesheet forumla

    Big Thank you. This seems to have solved all the problems within this worksheet.

    Hils

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Forumla with Forumla assigned- Help needed
    By MarkoPolo in forum Excel General
    Replies: 3
    Last Post: 03-06-2014, 06:46 PM
  2. Timesheet and V-Look ups
    By dawnvolk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-09-2014, 03:29 PM
  3. [SOLVED] Creating a timesheet for a charity project - forumla help :)
    By acather96 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-02-2012, 02:53 PM
  4. Timesheet help
    By kimmyrt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-22-2005, 01:06 AM
  5. Replacing a cell's forumla with the forumla's results?
    By Mooncalf in forum Excel General
    Replies: 2
    Last Post: 01-04-2005, 12:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1