Excel Time Calculations
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Excel Time Calculations

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Alaska
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    How do I subtract 0:30am (next day) from 22:30pm? Thanks!

    [ This Message was edited by: test on 2002-04-18 15:55 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 15:54, test wrote:
    How do I subtract 0:30am (next day) from 22:30pm? Thanks!

    [ This Message was edited by: test on 2002-04-18 15:55 ]
    use ABS() (absolute value) around the subtraction:

    if a1 = 22:30 and a2=0:30

    abs(a1-a2)



    [ This Message was edited by: John McGraw on 2002-04-18 16:00 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 15:54, test wrote:
    How do I subtract 0:30am (next day) from 22:30pm? Thanks!

    [ This Message was edited by: test on 2002-04-18 15:55 ]
    22:30 today? tomorrow? yesterday?
    is that 0:30 tomorrow minus 22:30 today? that would end up in a negative timevalue?!
    "Have a good time......all the time"
    Ian Mac

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

    Default

    On 2002-04-18 15:54, test wrote:
    How do I subtract 0:30am (next day) from 22:30pm? Thanks!

    [ This Message was edited by: test on 2002-04-18 15:55 ]
    I think you mean:

    =B2+(B2
    Custom format the cell of this formula as:

    [h]:mm

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Alaska
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    22:00 is today and 0:30 is tomorrow

    =B1+(B1 this function really worked, thanks a lot!
    Excel beginner like me would use something really long like this =if(b1

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-19 08:06, test wrote:
    22:00 is today and 0:30 is tomorrow

    =B1+(B1 this function really worked, thanks a lot!
    Excel beginner like me would use something really long like this =if(b1
    Aladin, is leveraging Excel's ability to "coerce" one data type into another. =B1
    =FALSE+0 is 0
    =TRUE+0 is 1

    The expression, =B1+(B1
    Other examples of coercion include...

    =1&"" produces "1"
    ="1/1/02"+0 produces 37257, the date value for January 1st.
    ="6:00"+0 produces 0.25, the time value for 6:00 AM.

    And, while we're discussing the effect of arithmetic operations on boolean values...

    I should also mention that the + operator when used in logical expressions is the equivalent of OR. The * operator is the quivalent of AND.

    =IF(TRUE+FALSE,1,0) produces 1
    =IF(TRUE*FALSE,1,0) produces 0

    ...just like...

    =IF(OR(TRUE,FALSE),1,0) produces 1
    =IF(AND(TRUE,FALSE),1,0) produces 0

    [ This Message was edited by: Mark W. on 2002-04-19 09:28 ]

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