Why are dates so tricky???
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Why are dates so tricky???

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    London
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am a newbie to excel and wouldn't be able to do anything like VBS that I saw mentioned in other posts that I read whilst trying to find an answer to this question...

    I have patients who may have a reaction to a drug. The event will have a start date and time(A1) and a stop date and time(B1). I want to calculate the duration.
    Initially I had both cells formatted as date with custom 'dd-mmm-yy hh:mm' and the calc as B1-A1 but if the month of start and finish is the same the result says that month is 1 when it should be zero.

    Is that clear enough??

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

    Default

    download the file against the name
    luanne.. name of the file is "Ship Time"

    it is just for beginners..

    i hope it will help you

    ni****h desai
    http://www.pexcel.com

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What format do you want B1-A1 to be in? You have to realize that Excel treats dates/times as fractions of a day. So if:
    A1=4/17/2002 9:21
    B1=4/18/2002 9:21
    B1-A1=1 (1 day)
    (B1-A1)*24=24 (24 hours)
    (B1-A1)*1440=1440 (1440 minutes)
    (B1-A1)*86400=86400 (86400 seconds)

    _________________
    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-04-18 07:28 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    London
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al et al,

    Thank you for the replies.

    If I format the result as dd mm then using your numbers, Al, it will give 01-jan (as jan is month 1!)


  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 08:09, vidius wrote:
    Al et al,

    Thank you for the replies.

    If I format the result as dd mm then using your numbers, Al, it will give 01-jan (as jan is month 1!)
    Actually, it will give you 01-jan (as jan-01 is the first day of the year)

    And if the result was 32 it would give you feb-01 (as feb-01 is the 32 day of the year)

    [ This Message was edited by: Al Chara on 2002-04-18 08:20 ]

  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-18 07:10, vidius wrote:
    I am a newbie to excel and wouldn't be able to do anything like VBS that I saw mentioned in other posts that I read whilst trying to find an answer to this question...

    I have patients who may have a reaction to a drug. The event will have a start date and time(A1) and a stop date and time(B1). I want to calculate the duration.
    Initially I had both cells formatted as date with custom 'dd-mmm-yy hh:mm' and the calc as B1-A1 but if the month of start and finish is the same the result says that month is 1 when it should be zero.

    Is that clear enough??
    Perhaps what's "tricky" about dates is Excel's proclivity to apply the date formatting of the source (precedent) data to the cell containing the resultant (dependent) data.

    For example, if A1 contains the date/time value for 4/15/02 6:00 and B1 contains the date/time value for 4/18/02 20:00 then the formula, =B1-A1, in cell C1 will be initially displayed as 1/3/00 14:00. Perhaps that's why you concluded that "the month is 1".

    In fact, C1 contains the value, 3.58333333333576, which represents the number of days between these two date/time values, but has inappropriately adopted the formatting used by both A1 and B1. In this cause you must expressly change the formatting to General to display the proper results.

    [ This Message was edited by: Mark W. on 2002-04-18 08:20 ]

  7. #7
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

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