Why are dates so tricky???

[vidius]

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??

[nisht]

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

nishith desai
http://www.pexcel.com

[Al Chara]

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 ]

[vidius]

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!)

[Al Chara]

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 ]

[Mark W.]

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 ]

[lenze]

http://www.cpearson.com/excel/datetime.htm#AddingDates