Convert time to a number when linking?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DavidJ726 - 31 May 2006 16:47 GMT

I'm referencing a time value in Worksheet "A" from the Data worksheet "B"
like this; =Data!A1. Cell A1 is formatted as time and shows as 01:00, and
I need it to be a general number (just a 1) in Worksheet "A" so I can divide
it by another general number. Cells B1 & B2 are formatted as general in the
source worksheet.

In other words, the difference between 1:00 (A1) and 8:00 (A2) is 7. At
1:00 the temp was 32 (B1) degrees and at 8:00 the temperature was 152 (B2)
degrees. In these 7 minutes the temperature rose 120 degrees. What I'm
trying to show is the delta, ie..
=(B2-B1)/(A2-A1) and the value should be 17.14 degree's per minue. However,
because of the time format the calculation does not work.

I'm not seeing anything in the help files for a conversion of this nature,
is it possible?

Thanks,
David

Reply to this Message

kcc - 31 May 2006 17:06 GMT

Time and dates are measured in days. For minutes you want (A2-A1)*24*60.
kcc

> I'm referencing a time value in Worksheet "A" from the Data worksheet "B"
> like this; =Data!A1. Cell A1 is formatted as time and shows as 01:00,
[quoted text clipped - 14 lines]
> Thanks,
> David

Reply to this Message

DavidJ726 - 31 May 2006 18:16 GMT

Thanks kcc, much appreciated. All I had to do was a little "nesting" and
it worked perfectly...
=(P27-P26)/((O27-O26)*24*60)

Regards,
David

> Time and dates are measured in days. For minutes you want (A2-A1)*24*60.
> kcc
[quoted text clipped - 17 lines]
>> Thanks,
>> David

Reply to this Message