+ Reply to Thread
Results 1 to 8 of 8

Need Formula Help ~ Converting Military Time in Time Card

  1. #1
    Registered User
    Join Date
    05-15-2009
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question Need Formula Help ~ Converting Military Time in Time Card

    Hello~

    What is the best way to convert military time so that we don't get a negative number in my total column? Attached is a timecard I am working on for my employer. I have attached the file so you can see what I am talking about. Can anyone help me with formulas to fix these? I am using excel 2003.

    We have 2 issues:
    1. We need the time card to convert military time for one department (see examples for May 2nd & 3rd). Several overnight shifts for this department.

    2. I have listed also examples on April 27th & 28th that do not calculate using regular time Example: when working a Noon shift (no military time). Or a shift that goes from 10am to 5pm.

    I have no idea how to fix this so it works!

    I hope I have given you enough information!

    Thank you in Advance!
    Jen
    Attached Files Attached Files
    Last edited by ORGIRL; 05-18-2009 at 01:03 PM.

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Need Formula Help ~ Converting Military Time in Time Card

    I think u can use ABS function...
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, «Born in USSR»
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Need Formula Help ~ Converting Military Time in Time Card

    Perhaps you want:

    H7: =MOD(E7-D7,12)+MOD(G7-F7,12)
    copied down

  4. #4
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Need Formula Help ~ Converting Military Time in Time Card

    Hi Jen

    I have posted back an amended copy of your file.
    I have only dealt with the May sheet, and I have shown 2 versions side by side.

    The first version
    All of the relevant cells have been altered in format as Format>Cells>Number>Custom>hh:mm

    The sample data has been re-entered using the established time format e.g. 07:00, 11:00, 12:00, 16:00
    Where they are Total cells, and the values within them could exceed 24 hours, they have been Custom Formatted as [h]:mm which allows the hours to roll past 24

    The formula in cell H7 and down that column has been modified to
    =MOD(E7-D7,1)+MOD(G7-F7,1)
    This deals correctly with times that cross the midnight threshold, without creating negative numbers.

    However, on the right hand side of your Timesheet, where you are allocating, and at the bottom where you want to multiply some values by 1.5, you may prefer to remain in ordinary Numbers and decimal time 8.5 as opposed to 8:30

    I have made a copy of your form and pasted it to the right of the existing entries.
    The Time input columns D,E,F and G have been formatted as Time (see above).
    The remaining cells have been left as Number with 2 decimal places.
    The formula in cell Aa7, and down that column, has been modified to
    =(MOD(X7-W7,1)+MOD(Z7-Y7,1))*24

    This is because Excel stores times as fractions of a day, so to convert to decimal hours we need to multiply by 24.

    You can multiply these values directly by value per hour, by overtime rates etc. without problem.

    If you decide to stick with all entries being in a Time format, just remember to multiply your formulae by 24.
    Attached Files Attached Files
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  5. #5
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Need Formula Help ~ Converting Military Time in Time Card

    I think u can use ABS function...

  6. #6
    Registered User
    Join Date
    05-15-2009
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Need Formula Help ~ Converting Military Time in Time Card

    Quote Originally Posted by ContaminatedWitExcel View Post
    I think u can use ABS function...
    Thank you for responding. I would need some further details as to how to use the ABS function as I have never used it before. I only know excel basics and by no means am I an expert. Could you kindly give me a little more help? And maybe show me how to create the formula that works?

    p.s. my excel file is attached to my original post

    Thank you!

  7. #7
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Need Formula Help ~ Converting Military Time in Time Card

    Ctually I think that my idea is bad in this situation

    Roger Govier and DonkeyOte wrote formula you need... which work perfectly

  8. #8
    Registered User
    Join Date
    05-15-2009
    Location
    Oregon
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Need Formula Help ~ Converting Military Time in Time Card

    Thank you!!! These are great! Exactly the help I was needing!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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