+ Reply to Thread
Results 1 to 12 of 12

time sheet help, format TIME and formula based on the actual time entered.

  1. #1
    Registered User
    Join Date
    12-01-2016
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    time sheet help, format TIME and formula based on the actual time entered.

    I have a few questions and hope anyone can help me with it. If you think I can be helped with macros please let me know and I’ll move my question over there.
    I’m working with employee time cards, which I downloaded in excel from our program. From some reason, the values in column A and B cannot be changed to TIME format (in the ‘downloaded info’ sheet)*. The reason I need it to be time, is because I need another formula to show that if an employee clocked in after 9pm the time worked should return as 0. For ex, D2 should be 0.
    Or, if the format can’t be changed, I could continue to work with the number the program give me (column C) and then add a separate formula that if the value in A2 is after 9pm, the hours worked should be 0 (it doesn’t necessarily have to change in column C, I could have it somewhere else, because In reality I’m working with 2 workbooks, one with the original info downloaded, and the second one which references all the times and I have the formulas there to calculate the pay.)
    Not sure any of these are possible, but appreciate any feedback.

    *I did a test (see sheet ‘test’) which I manually entered times, and then did Replace pm with space pm and that worked to change to TIME format (see sheet ‘test2’) but for the downloaded info (in the ‘downloaded info’ sheet) it’s not working.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: time sheet help, format TIME and formula based on the actual time entered.

    Try

    in C2 ("Downloaded info")

    =IF(A2="","",MOD((TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($B2,"pm"," pm"),"am"," am"))))-(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))),1))

    copy down

    format as "time" : hh:mm
    Last edited by JohnTopley; 12-05-2016 at 04:13 PM.

  3. #3
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: time sheet help, format TIME and formula based on the actual time entered.

    Hi
    Use this formula to convert your download time into real time (Convert column A and column B)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    12-01-2016
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: time sheet help, format TIME and formula based on the actual time entered.

    thank you for your responses! Jose, yours worked perfect, thanks! John, yours works as well (and it does save a step) however the outcome of your formula is time, and the actual hours worked I need as a general number. Can you change that? Also, any input that after 9pm the hours worked should be 0?

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: time sheet help, format TIME and formula based on the actual time entered.

    actual hours worked I need as a general number
    2:17 (hh:mm) as a number 2.17 is nonsensical, especially if you SUM it.

    2:45 + 4:30 = 7:15 not 6.75 as a number

    Figures from total of Days 1 to 3

    Number hh:mm
    10.80 11:20
    10.87 11:27
    10.37 10:37

    For after 9 pm start:

    =IF(A2="","",IF(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))>21/24,0,MOD((TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($B2,"pm"," pm"),"am"," am"))))-(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))),1)))

  6. #6
    Registered User
    Join Date
    12-01-2016
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: time sheet help, format TIME and formula based on the actual time entered.

    2:17 (hh:mm) as a number 2.17 is nonsensical, especially if you SUM it.I'm trying to round it, not sum it.
    =if(c2>=2,2,0)
    will look at the other formula now

  7. #7
    Registered User
    Join Date
    12-01-2016
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: time sheet help, format TIME and formula based on the actual time entered.

    Quote Originally Posted by bh mng View Post
    2:17 (hh:mm) as a number 2.17 is nonsensical, especially if you SUM it.
    I'm trying to round it, not sum it.
    =if(c2>=2,2,0)
    will look at the other formula now

  8. #8
    Registered User
    Join Date
    12-01-2016
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: time sheet help, format TIME and formula based on the actual time entered.

    Quote Originally Posted by bh mng View Post
    For after 9 pm start:
    this worked perfect, you're amazing! but same issue as before, if I get a time value I can't round that number. Our guidelines for pay are a bit different as conventional workplaces that's why I need these type of equations.
    When I'll have time I'll study exactly what you did there, as it is worth knowing it...

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: time sheet help, format TIME and formula based on the actual time entered.

    So what is the rule regarding rounding?

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: time sheet help, format TIME and formula based on the actual time entered.

    This will round down i.e ignore the minutes: format cells as General or Number

    =IF(A2="","",IF(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))>21/24,0,INT(MOD((TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($B2,"pm"," pm"),"am"," am"))))-(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))),1)*24)))

  11. #11
    Registered User
    Join Date
    12-01-2016
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: time sheet help, format TIME and formula based on the actual time entered.

    Worked thank you so much.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: time sheet help, format TIME and formula based on the actual time entered.

    You are very welcome and thank you for the feedback and rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. [SOLVED] Converting an elapsed time in decimal number format to an actual time :S
    By Spicey_888 in forum Excel General
    Replies: 3
    Last Post: 07-20-2014, 08:53 PM
  2. Replies: 5
    Last Post: 04-18-2013, 11:31 AM
  3. Actual Hold Time and Actual Talk Time
    By MaunishP in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2013, 08:15 AM
  4. Replies: 3
    Last Post: 03-27-2012, 01:07 PM
  5. Replies: 4
    Last Post: 07-14-2010, 03:17 PM
  6. [SOLVED] How to sync my received time with the actual time?
    By NJD in forum Excel General
    Replies: 3
    Last Post: 08-22-2005, 04:05 AM
  7. [SOLVED] I need a time sheet template that verifies time entered against sy
    By Bob Powell in forum Excel General
    Replies: 1
    Last Post: 04-19-2005, 11:06 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