Results 1 to 9 of 9

Time calcalulations in military time

This is a discussion on Time calcalulations in military time within the Excel Questions forums, part of the Question Forums category; Hello again. I am trying to calculate how long it took someday to complete a job. Such a a doctor ...

  1. #1
    New Member
    Join Date
    May 2011
    Posts
    6

    Default Time calcalulations in military time

    Hello again.
    I am trying to calculate how long it took someday to complete a job. Such a a doctor dictates 1707 and it is typed at 1802, I have tried it all and just am not getting it. Rarely it will go over into the next day, but that is the least of my worries at the moment. I will attempt to copy and paste my worksheet here.
    Time DictatedTime Transcribed
    17071802
    08360941
    10391232
    16261858
    16041757
    19382040
    13231409
    18202117
    18250241
    17170015
    09501155
    16442113
    10551245
    none
    17212004
    17042238
    09141036
    16401938
    08520956
    13121843
    17080108
    none
    14361856
    none
    none
    18222307
    16362014
    16281908
    12061243
    20242111
    09561305
    20382115
    none
    10561950
    20202057
    none
    18562002
    none
    09181630
    16351949
    17181758
    17101741
    17372000
    12041457
    10261203
    10221212
    none
    13481443
    none
    22412328
    none
    11341152
    14021504
    none
    none
    none
    10301223
    none
    17232156
    17262042
    17541916
    none
    none
    08170936
    14361623
    20050107
    12381258
    17381822
    none
    none
    none
    19022017
    none
    20410127
    15401638
    none
    12341600
    none
    09251140
    05050628
    15131932
    16542158
    19512217
    08151014
    13581540
    20542231
    none
    none
    09391334
    none
    13141805
    none
    none
    none
    20162336
    14541904
    18292127
    11571729
    15541741
    14071507
    08430939
    06380912
    18542048
    18432033
    none
    01120311
    15501229
    00220202
    02170639
    none
    16281730
    16262148
    08401007
    21212348
    09211214
    01090209
    20362206
    16562330
    11381357
    15351906
    13431846
    00411005
    20442244
    08410916
    21512236
    09441253
    13261441
    12412026
    09492216
    23360616
    none
    17061853
    none
    17091913
    01070245
    01410416
    10471211
    21462244
    20482228
    00040212
    17190214
    10561549
    13001503
    15131934
    01140253
    11331211
    10371206
    09361206
    17132045
    15451712
    none
    08581025
    08300958
    09261220
    09071308
    14451535
    00580204
    09181034
    none
    06060806
    19522024
    09191243
    10241155
    13451755
    13371556
    04160755
    none
    20542302
    01010224
    00090344
    20152115
    17512037
    18081957
    20000028
    17511902
    15131948
    08150856
    none
    19062325
    11121553
    00380205
    10041156
    03361003
    16251722
    06510811
    none
    01230327
    12141537
    13051708
    20570046
    23150021
    16511857
    05580738
    16061724
    01200200
    11551258
    09031319
    19232136
    15301736
    22370055
    18582120
    23310106
    17231950
    21030040
    08521011
    08401026
    20552226
    22460018
    01070214
    17371857
    22070100
    08581328
    14261555
    10321234
    10231203
    23370218
    22022307
    14261644
    17452009

    thank you
    April

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

    Default Re: Time calcalulations in military time

    What you posted are not true time values.

    If they were

    =MOD(B1-A1,1)

  3. #3
    New Member
    Join Date
    May 2011
    Posts
    6

    Default Re: Time calcalulations in military time

    How do I post a true time value? I have to put the cells into a text format because if I typed in 0700, it removed the 0 in front. If I typed 2400, it removed the 00.
    I just get by in Excel, so excuse my lameness, and spelling too!

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

    Default Re: Time calcalulations in military time

    Quote Originally Posted by aprilsea View Post
    How do I post a true time value? I have to put the cells into a text format because if I typed in 0700, it removed the 0 in front. If I typed 2400, it removed the 00.
    I just get by in Excel, so excuse my lameness, and spelling too!
    you need to enter with a colon

    23:00

    although 0100 will appear as 1:00

    Or you could convert the text times with

    =TEXT(A1,"00\:00")+0

    Or nesting the MOD formula

    =MOD((TEXT(B1,"00\:00")+0)-(TEXT(A1,"00\:00")+0),1)
    Last edited by Brian from Maui; Jan 31st, 2012 at 07:01 PM.

  5. #5
    New Member
    Join Date
    May 2011
    Posts
    6

    Default Re: Time calcalulations in military time

    Brian,
    You are awesome! If you werent in Maui I would buy you dinner.
    I do have one question. The calcaluations that go past the hour of 2400 or 0000 midnight seem to lose the count of one hour. Is that possible?
    I cannot tell you how many hours you saved me.
    Do all of you help others such as myself because you just love Excel?
    Again, thankyou!!!!!
    April

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

    Default Re: Time calcalulations in military time

    Quote Originally Posted by aprilsea View Post
    Brian,
    You are awesome! If you werent in Maui I would buy you dinner.
    I do have one question. The calcaluations that go past the hour of 2400 or 0000 midnight seem to lose the count of one hour. Is that possible?
    I cannot tell you how many hours you saved me.
    Do all of you help others such as myself because you just love Excel?
    Again, thankyou!!!!!
    April
    Lose an hour? Can you give an example?

    Not sure if midnight is expressed as 24:00 or 00:00.

  7. #7
    New Member
    Join Date
    May 2011
    Posts
    6

    Default Re: Time calcalulations in military time

    Lets say they logged in at 2100 and logged out at 0200. That would be 5 hours, but the formula is counting four.
    The time should read as follows
    2100
    2200
    2300
    0000
    0100
    0200

    does this make sense?
    April
    again thank you

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    2,727

    Default Re: Time calcalulations in military time

    Try custom formatting the cells (in this case column C) to [h]:mm:ss

    Sheet2

    *ABC
    121:0023:002:00:00
    222:002:00:004:00:00
    323:0004:005:00:00
    40:00:0003:003:00:00
    501:0003:002:00:00
    602:0004:002:00:00

    Spreadsheet Formulas
    CellFormula
    C1=MOD(B1-A1,1)
    C2=MOD(B2-A2,1)
    C3=MOD(B3-A3,1)
    C4=MOD(B4-A4,1)
    C5=MOD(B5-A5,1)
    C6=MOD(B6-A6,1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Using Excel 2010 32 bit on Windows 64 bit

    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please try & follow the posting Rules and Guidelines & please use CODE tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links:

    Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

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

    Default Re: Time calcalulations in military time

    Quote Originally Posted by aprilsea View Post
    Lets say they logged in at 2100 and logged out at 0200. That would be 5 hours, but the formula is counting four.
    The time should read as follows
    2100
    2200
    2300
    0000
    0100
    0200

    does this make sense?
    April
    again thank you
    What formula are you using for this calculation?

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