Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Military time used in calculating payroll hours

This is a discussion on Military time used in calculating payroll hours within the Excel Questions forums, part of the Question Forums category; I have payroll hours in military time. It is in 100th of a minute. I need to: enter beginning hours, ...

  1. #1
    New Member
    Join Date
    Oct 2002
    Posts
    45

    Default Military time used in calculating payroll hours

    I have payroll hours in military time. It is in 100th of a minute.
    I need to:

    enter beginning hours, in military time
    enter ending hours, in military time

    calculate actual hours elapsed, rounded to the nearest quarter hour


    Can anyone help me?

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default Re: Military time used in calculating payroll hours

    =MROUND(b1-a1,1/(24*4))

    ...where B1 contains end time & a1 start. assumes:

    1) entries are held as genuine time values
    2) times do not cross midnight, or if they do they are associated with a date value.
    3) analysis tool pack is installed (tools | addins)

  3. #3
    New Member
    Join Date
    Oct 2002
    Posts
    45

    Default Re: Military time used in calculating payroll hours

    I guess I'm more confused than I thought.

    How do I enter the military time? What format do I use?

    As an example: Beginning = 16:90

    Ending = 0:38 -- This will also cross over a date.

    I do have add-ins installed, but I'm not sure how this fits into the problem.

    Any help is greatly appreciated!

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Military time used in calculating payroll hours

    Hi jzuber:

    Military time would be entered in 24 hour format without the need to enter AM or PM. So let us say I start work today (Aug-06-2003) at 16:45 and I work till 00:30 hour. Then my ...

    Start Time is entered as 8/6/03 16:45
    and
    Quit Time is entered as 8/7/03 00:30

    I hope this helps. If I have misunderstood your question -- my apologies!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    New Member
    Join Date
    Oct 2002
    Posts
    45

    Default Re: Military time used in calculating payroll hours

    Hi Yogi:

    Thanks for taking the time to help me out. I think the beginning of my confusion is keying the date into excel.

    When I enter the time (ie., 15:84) excel automatically associates a date with it .... or tries to. How do I format the column?

    Do I enter a date, then the time in the same cell?

    As you can see, I'm quite confused.

    Thanks again, JAZ

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Military time used in calculating payroll hours

    Hi jzuber:

    Excel handles Time as part of the day -- think about it a day has 24 hours, so an hour is 1/24 of a day, and so on.

    Now in regard to entering Date and Time ...

    Date and Time can be entered separately or together -- but please bear in mind since time is part of a day, whether these are entered together or separately may be a matter of need or preference. The biggest confusion comes in the Formating aspect -- Date and Time can be formated in different ways -- but regardless of how something is formated, its underlying value remains unchanged.

    Please look at the following spreadsheet simulation ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y030806h1.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    ****
    2
    StartTime8/6/03*16:45**
    3
    QuitTime8/7/03*0:30**
    4
    ****
    Sheet6*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    In cell B2, I entered the date, then a space and then hours, then a : and then minutes

    Does it help?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,227

    Default Re: Military time used in calculating payroll hours

    Quote Originally Posted by jzuber
    Hi Yogi:

    Thanks for taking the time to help me out. I think the beginning of my confusion is keying the date into excel.

    When I enter the time (ie., 15:84) excel automatically associates a date with it .... or tries to. How do I format the column?

    Do I enter a date, then the time in the same cell?

    As you can see, I'm quite confused.

    Thanks again, JAZ
    15:84 is not a valid time in Excel. Are you wanting to convert time to decimals? If so, multiply by 24 and format cell as a number with two decimal places. 15.84 is approx 15:50

    Edit:

    Didn't see you Yogi, my apologies

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Military time used in calculating payroll hours

    Hey No worries, Brian!

    I am hoping that Jaz would actually enter the time based on my simulation -- that is why I mentioned Hours and Minutes separated by : -- so come time to actually making the entries, Jaz will say, Ah! but we don't talk about 84 minutes while talking about clock time in Hours and Minutes.

    We all have to pick things up at our own pace!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    New Member
    Join Date
    Oct 2002
    Posts
    45

    Default Re: Military time used in calculating payroll hours

    I finally understand what I was doing wrong and can see how this will work. I'm still not able to calculate the difference between ending and beginning hours though.

    Using 15:84 as my start time, my clock calculates portions of an hour in 100th of a minute, so do I need to convert this to actual minutes (ie., x/60=84/100, which gives me 50.4 minutes) before I can calculate the elapsed time?

    I used the MRound formula, but I didn't get anything close to the right time.

    I'm really looking for something simple, so I don't want to have to enter the dates and hours, etc. I considered a very long IF statement to convert to normal time, then just subtract the time. Am I heading for the ozone yet?

    JAZ

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Military time used in calculating payroll hours

    Hi Jaz:

    Let us go systematically -- once we have got the basics pat, it would be smooth sailing. As I mentioned in my posts as well as in my simulation(s), time is shown with hours and then a colon and then minutes, so since there are 60 minutes in an hour, we do not talk of time as being 15:84

    Now if you hav time as 15 hour and 84/100 of an hour that is not shown as 15:84, but instead it will be shown as 15.84 hours -- Fair enough! Excel has its own lingo, and if we are going to work with Excel, the sooner we get past these nuances, the better.

    So, if you are still with me,

    tell me what is the StartTime
    whether you tell it to me in Hours and Minutes, or in Hours and decimals of an hour, be clear about it and know the difference

    and then tell me what is the QuitTime
    again whether you tell it to me in Hours and Minutes, or in Hours and decimals of an hour, be clear about it and know the difference

    Once we clearly know what is the QuitTime, and what is the StartTime, how long one actually worked is simply a matter of subtracting the StartTIme from the QuitTime

    So, provide the requested information -- and then let us take it from there.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com