+ Reply to Thread
Results 1 to 7 of 7

24 hour military time sheet help please

  1. #1
    Registered User
    Join Date
    04-27-2017
    Location
    Australia
    MS-Off Ver
    latest
    Posts
    4

    Question 24 hour military time sheet help please

    Hi please help! I have spent most of today searching on the net and trying to fix this time sheet.

    My finance lady needs to SEE the timesheet in 24 hour time.
    I need the total time, minus the lunch break calculated, and the answer shown as a number (ie 10 and 30 minutes would look like 10.5, 10 hours and 45 minutes would show as 10.75).

    Take a look at this screen shot:
    timesheet pic.PNG

    I've attached the sheet and you can see it here:

    https://drive.google.com/open?id=0Bz...0VNeXRLN1pDb2c

    Is there something I am doing wrong here?

    Kind regards

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: 24 hour military time sheet help please

    One way if you data is truly text. Add formula in F2

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: 24 hour military time sheet help please

    Hellow Jameshasnobrains, Time calculation always format as Time (h:mm)
    Look attach file i have done modification in time i.e. 6:00 / 12:00 / 12:30
    Also inseart one "F" column to convert time into decimal.
    "G" column formula =(Finish Work - Start Time) - (Lunch Finish - Lunch Start)
    In "F" column =(Total Paid Hours)*24 (For showing Decimal)
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: 24 hour military time sheet help please

    Looks like your time is not really time, it is text, looking like time. Where is this data coming from?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    04-27-2017
    Location
    Australia
    MS-Off Ver
    latest
    Posts
    4

    Cool Re: 24 hour military time sheet help please

    Wow thanks for your help guys!

    I changed the military time format to be: hhmm
    This way it is shown in 24 hour format.

    Then I combined the formula I was given from you handy guys, with what I already had; =((E3-B3)-(D3-C3))*24 I also made the format to be general.
    This is for the totals, this way it is shown as a decimal number, and not a time. For example if I worked 8 hours ad 45 minutes, the daily total would red 8.75.
    This is really great because when I calculate my earnings, I just times the totals by the hourly rate.

    BRILLIANT!!

    Now there is just one tiny problem to fix, and then this will be perfect. Currently i need to insert time by typing like this:

    06:00 this will then turn into 0600 with this time sheet.

    But inputing all these semicolons ( is really annoying. I am trying to streamline things with this excel sheet as much as I can.

    PCMAG says this: "To enter HH:MM time values without the colon, you'll simply use a different custom format. The format 0\:00 will give one- or two-digit hours, while the format 00:\00 will always give you two-digit hours, left-padding with a zero if necessary.

    I tried this, but it does not work. Any ideas?

    Here's my current xcel sheet if anyone wants a crack at it, or to use it

    https://drive.google.com/open?id=0B-...FZOUE1FWnk4T0U

  6. #6
    Registered User
    Join Date
    04-27-2017
    Location
    Australia
    MS-Off Ver
    latest
    Posts
    4

    Re: 24 hour military time sheet help please

    NEVER MIND I'VE JUST CRACKED IT!!
    I feel like we've just discovered something here.
    If I format the time cells as text, and then use part of of mike7052's formula (Sorry Mike, yours would not calculate the lunch break properly), which is brilliant btw as we can then calculate text format!

    Have a squiz if you want to see this work of art:

    =((TEXT(K3,"00\:00")-TEXT(J3,"00\:00"))+(TEXT(M3,"00\:00")-TEXT(L3,"00\:00")))*24

    If anybody wants this timesheet, here you. Sharing is caring :D

    https://drive.google.com/open?id=0B-...FZOUE1FWnk4T0U

  7. #7
    Registered User
    Join Date
    04-27-2017
    Location
    Australia
    MS-Off Ver
    latest
    Posts
    4

    Re: 24 hour military time sheet help please

    Hi, yes, I was trying to display what I wanted the timesheet to look like in the first two rows. I found a lot of threads where the time ends up looking like this: 6:45 AM or even 06:45 and not this: 0645

+ 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. add number of hour to time in military format
    By mazan2010 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-25-2016, 05:05 AM
  2. [SOLVED] Extract hour from TIME in NON-Military time
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-17-2015, 08:45 AM
  3. [SOLVED] Covert date/time to military time hour only
    By toontown in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-03-2013, 01:05 PM
  4. Military/24 Hour Time Adding and difference finding
    By Squirrel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2011, 11:05 PM
  5. Military time range to decimal of hour
    By slopland in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-31-2010, 03:25 AM
  6. [SOLVED] How to I convert standard time to Military or 24 hour format?
    By Nacho in forum Excel General
    Replies: 5
    Last Post: 06-28-2006, 02:20 PM
  7. Rounding military time to nearest tenth of an hour
    By rcterrell in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-24-2005, 10:25 PM

Tags for this Thread

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