Hours worked - Military Time - Expressed as a decimal...
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Hours worked - Military Time - Expressed as a decimal...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi
    My time is in four digit format with no colon.
    0600
    0715
    2345
    ect...

    The value I am trying to get is hours worked expressed as a decimal...

    For example:

    2315 starting time
    0730 ending time

    Formula would display:
    8.25

    I know beggars can't be choosers, but I would prefer an answer in VBA, but will gladly take an excel in the cell answer!

    Thanks!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-23 05:25, TsTom wrote:
    Hi
    My time is in four digit format with no colon.
    0600
    0715
    2345
    ect...

    The value I am trying to get is hours worked expressed as a decimal...

    For example:

    2315 starting time
    0730 ending time

    Formula would display:
    8.25

    I know beggars can't be choosers, but I would prefer an answer in VBA, but will gladly take an excel in the cell answer!

    Thanks!
    If your text-formatted times are always 4-digits, the following would do what you want.

    in C2 enter: =(LEFT(A2,2)&":"&RIGHT(A2,2))+0
    in D2 enter: =(LEFT(B2,2)&":"&RIGHT(B2,2))+0

    in E2 enter: =(D2+(D2
    where A2 houses a 4-digit start time and B2 a 4-digit end time.

    You could also, if desired, plug in C2 and D2 in the formula of E2.

    Aladin


    [ This Message was edited by: Aladin Akyurek on 2002-03-23 05:55 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    2315 0730 = 8.25
    0730 1500 = 7.50
    0800 1720 = 9.33
    2359 0059 = 1.00
    0001 2301 = 23.00
    1550 1450 = 23.00

    All perfect!
    Thanks Aladin!

    Would you walk another mile with me for two more questions?

    in C2 enter: =(LEFT(A2,2)&":"&RIGHT(A2,2))+0
    in D2 enter: =(LEFT(B2,2)&":"&RIGHT(B2,2))+0
    in E2 enter: =(D2+(D2

    Why the +0 on the ends of the first two?
    I do not understand the third???
    Especially (D2 Thanks. I appreciate and hope that I might return the favor someday. Though looking at your posts, it might be a while!


    [ This Message was edited by: TsTom on 2002-03-23 06:17 ]

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-23 06:16, TsTom wrote:
    2315 0730 = 8.25
    0730 1500 = 7.50
    0800 1720 = 9.33
    2359 0059 = 1.00
    0001 2301 = 23.00
    1550 1450 = 23.00

    All perfect!
    Thanks Aladin!

    Would you walk another mile with me for two more questions?

    in C2 enter: =(LEFT(A2,2)&":"&RIGHT(A2,2))+0
    in D2 enter: =(LEFT(B2,2)&":"&RIGHT(B2,2))+0
    in E2 enter: =(D2+(D2

    Why the +0 on the ends of the first two?
    I do not understand the third???
    Especially (D2 Thanks. I appreciate and hope that I might return the favor someday. Though looking at your posts, it might be a while!

    [ This Message was edited by: TsTom on 2002-03-23 06:17 ]
    +0 coerces (forces) Excel to treat here the result of the formula as number. The existence of ":" makes Excel to recognize the result as time.

    (D2
    07:30 is represented internally as 0.3125.
    23:15 as 0.96875.

    1.3125 - 0.96875 = 0.34375,

    which is the internal value of 8:15.

    8:15*24 converts 8:15 to decimal hours.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-23 06:49 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks again Aladin.
    Tis in me notes.
    Have a good day!

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin:
    Following up with a notes of THANKS from TsTom on your very insightful postings, I have a comment/question for you.
    Incorporation of D2 minus C2 in the formula adds tremendous value ... but naturally it will be limited to a 24 hour cycle.
    If I go beyond the 24 hour cycle ... say I start working on 3/23/02 11:30 and stop work on 3/25/02 7:30, I have no choice but to include the date along with the time, and follow up with normal subtraction. I don't know if it is even relevant here but I thought I might mention it anyway.
    Regards!

    _________________
    Yogi Anand
    Edit: Deleted reference to inactive web site from signature line

    [ This Message was edited by: Yogi Anand on 2003-01-19 18:48 ]

  7. #7
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha,

    I posted this question yesterday, and I'll try again. Our company deals in military time also. We (not personally) keeps a log, and one of the major complaints is inputting the colon. Using the custom format 00:00, they can't add/subtract times. Is it possible for a macro to add the colons and still be able to add/subtract. I'm not looking for someone to write the code, just if it's possible.

    Brian

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian

    ermm.. yep VBA im sure more of lett anything can be done this wioll be none to complex for a proper programmer, so input time as 6:26 or however and then VBA can convert to whatever you want. the subtaaction part is a case of making the time usable in math (Hey an American word) so you can calculate with it, this lies the problem.

    VBA would add columns convert as many times as needed and remover the now redundant columns used in convertion...

    thats the idea...

    You can in Excel use custom functions, know mostly as UDF (user definded function) say =SUM(... this might be =JACK( notice JACK in not in excel so we write a function to do WHATEVER we like... normal to convert data not so much math it.

    OK UDF are VERY VERY VERY slow, as they are normally set to target or range settings and so will be a nasty little thing to speed up.

    Saying that i have a few, if you like i can email you, bounce me a mail to work to remind me and ill set a sheet with some goodies in, convertions most or add something and remove something...

    I find in Excel most this can be done of this nature in open play.. Aladin the formula mistro has been posting an even without checking and testing his work i know it perfection, and a good start.

    If i was asked to make VBA in this i would convert Adadins answer in to a UDF or command button and use range settings, this would give cell or row/column of cleaned data...

    i would not deviate from Aladins answers as i know form nearly 2 years of trying to beat this answers its pointless.

    Its worth adding that fast codes against efficent codes and simple scripts do not mean they are the BEST.. i honest beleive its about exceptable resultys and this includes time.

    I have Script taht adds col converts add something the the string and copies and pastes as value and delets the original col ... how fast???

    OK 65500 cells in less than a second.

    IN UDF that same codes take????

    As long as it takes to copy paste and calculate... 5 seconds//////

    Does it matter?

    to me depends how i feel....... i happen to like a pop up saying a message to lost pals friends that have died so i remember them, or i love to type =Jack_Convert(

    just me.

    Take care friend...

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-23 09:59, Brian from Maui wrote:
    Aloha,

    I posted this question yesterday, and I'll try again. Our company deals in military time also. We (not personally) keeps a log, and one of the major complaints is inputting the colon. Using the custom format 00:00, they can't add/subtract times. Is it possible for a macro to add the colons and still be able to add/subtract. I'm not looking for someone to write the code, just if it's possible.

    Brian
    Hi Brian:
    We discussed this in yesterday's post that started with CJ, and we looked at a number of ways of doing this including using the custom format as you stated here. The custom format 00:00 works with numbers only, so there is a problem applying it with times with trailing 0's ... in which case there will be only 1 digit in the hours. So that will have to be taken into account.
    However, as discussed at the tailend of yesterday's thread, you can convert your time entry '0830 into time by
    '0830 =time(left(a2,2),right(a2,2),0))
    and then you can use the new entry to add / subtract, or whatever.
    If I had '0830 in cell A2, and '0600 in B2
    '0830 - '0600 in cell C2 ... result formula
    =time(left(a2,2),right(a2,2),0)-time(left(b2,2),right(b2,2),0)
    =2:30:00
    then if you want to format this as hours in decimal notation
    =(a2-b2)/24 formatted as NUMBER|General gives
    =2.5

    HTH
    Please post back if it works for you ... otherwise explain a little further and let us take it from there!

    _________________
    Yogi Anand
    Edit: Deleted inactive web site reference from hard coded signature

    [ This Message was edited by: Yogi Anand on 2003-01-19 15:49 ]

User Tag List

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