Convert time to a number when linking?

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




0
DavidJ726 (42)
5/31/2006 3:47:31 PM
excel 39879 articles. 2 followers. Follow

2 Replies
977 Views

Similar Articles

[PageSpeed] 22

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

"DavidJ726" <DavidJ726@hasnoaddress.com> wrote in message 
news:%23odfJmMhGHA.4252@TK2MSFTNGP04.phx.gbl...
> 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
>
>
>
> 


0
5/31/2006 4:06:50 PM
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

"kcc" <kcconline@comcast.NOSPAM.net> wrote in message 
news:X6idnUnf1-Y1IuDZnZ2dneKdnZydnZ2d@comcast.com...
> Time and dates are measured in days.  For minutes you want (A2-A1)*24*60.
> kcc
>
> "DavidJ726" <DavidJ726@hasnoaddress.com> wrote in message 
> news:%23odfJmMhGHA.4252@TK2MSFTNGP04.phx.gbl...
>> 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
>>
>>
>>
>>
>
> 


0
DavidJ726 (42)
5/31/2006 5:16:35 PM
Reply:

Similar Artilces:

Converting Claris Impact files (old Apple files) to M'soft Visio"
Hello I understand the the best way to access old Claris Impact files are to convert using m'soft visio. I'm completely new to this application & will be grateful if anyone can take me throughm the process of doing this. I've had a look at the Versiontracker website on salvaging claris impact files but I couldn't work out how it could help me! I really need some step by step help. I've got Visio 2007 installed on my XP Pro computer Many thanks Perhaps this link might help: http://www.thefreelibrary.com/Orbital+Successfully+Completes+File+Converter+to+Allow+Cla...

Converting Microsoft Office 2003 Trial into a full version
I have bought a Sony Vaio PCG-GRT360ZG notebook with installed Microsoft Office 2003. I registered Microsoft Office 2003 trial version. Now I am trying to convert it into a full version. Unfortunately I am getting message that my product key (from Certificate of Authenticity) is invalid. My trial version expires in Jan 2005. What should I do ? My product key from Certificate of Authenticity label is : J8FHJ-Q6BJM-G6PVD-FWM2B-PW6J8. Thank you very much Peter. ...

Converting Dates from YYMMDD to MMDDYYYY
Hi All; I'm looking for a format function that will convert a date in the text format of YYMMDD to text format of MMDDYYYY. Any help would be GREATLY appreciated! Thanks in advance! You can't do it using a Format function, but try: Function SwitchDateFormat(YYMMDD As String) As String Dim strDay As String Dim strMonth As String Dim strYear As String If Len(YYMMDD) = 6 Then strYear = Left$(YYMMDD, 2) strMonth = Mid$(YYMMDD, 3, 2) strDay = Right$(YYMMDD, 2) If strYear < "30" Then strYear = "19" & strYear Else strYear =...

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

Media players opens and keeps trying to refresh about 5 times a mi
I have done numerous spyware chaecks all showing nothing detected I have eliminated any options in the media player that would have it trying to check for updates or web items. Completely at a loss and extremely unhappy the microsoft site does not have an answer. Acer Exyenza Laptop with Win XP , Office 07 and player 11 any help guys? in the time in took to type this meassage the player popped up three times Try disabling (unchecking) all active WMP plug-ins in WMP's Options. If you download and run Process Explorer - http://technet.microsoft.com/en-us/sysinternals/bb8...

Won't go to link
Firefox 2.0 & Outlook 2003 When I have a link in an email to a certain website it does not go there when I click on it. I assume this is a setting in Firefox or Outlook. Help please. Thanks... Bob Bob Newman <bobnewman@cox.net> wrote: > Firefox 2.0 & Outlook 2003 > > When I have a link in an email to a certain website it does not go > there when I click on it. I assume this is a setting in Firefox or > Outlook. See if something here helps: http://www.slipstick.com/problems/firefox.htm -- Brian Tillman That is not it. I don't get any error messa...

Calculate number of months
Hello, I have a field where I am trying to implement a calculation. I want it to take the date in a cell and subtract it from today's date to show me the total number of months between those two dates. So right now, it looks like =(TODAY())-D3 and it returns 167 - the total number of days. How do I make it show me months? THANK YOU IN ADVANCE =Month(Today())-Month(D3) "TxWebDesigner" <beverly@beverlylanedesigns.com> schreef in bericht news:e3fR#jXWJHA.5032@TK2MSFTNGP05.phx.gbl... > Hello, > > I have a field where I am trying to implement a calculation...

How can I prioritize tasks by number in Outlook 2007?
I am trying to create a custom priority for my tasks in Outlook 2007, but I'm not able to edit the field that was created when I changed the settings to include a custom priority. I'm trying to put a number into it, so that I can sort my tasks in a certain category by number, but I'm not able to. We aren't watching you and your explanation is missing details. Where in Outlook are you trying to view the Tasks? There are 3 places to see Tasks. If in the Task Folder, what View are you in? If in Tasks folder you probably need to turn on in-line editing. Regards Judy Gle...

I have a problem with getting a total Sum of numbers (URGENT)
I'd really appreciate if someone could help me here. I am using Microsoft Excel 2002 on a Microsoft Office XP system. I a trying to develop a minor league baseball schedule, but am having som problems. I have attached a copy of my spreadsheet to give everyone some idea o what I'm doing here. I have where the fans list the "RUNS, HITS & ERRORS) of both teams an for each game of the series. I then have it set up in the TOTA WON/LOST column where if the T-Bones RUNS are more than the SALTDOG runs, a "1" appears in the WON column. If the SALTDOGS have a highe numbe...

Run-time error '381' Invalid property array index
Dear All, I am using RMS 2.0, In POS i set security (disable read) on all columns of setting Customer window. But i got this error when i press F7: Run-time error '381' Invalid property array index. Any kind of help will be highly appreciated. Ibrahim Zubair I get the same message when I try to reprint a receipt did you find the answer to this problem? "Ibrahim Zubair" wrote: > Dear All, > > I am using RMS 2.0, In POS i set security (disable read) on all columns of > setting Customer window. But i got this error when i press F7: > Run-time error '...

time formulas
Can anybody give me an advice Lets say i put in first column dime of departure and in second one time of arrival (of course i know have to insert the time ). In third column i get the time spent somwhere. So far everything is ok. The problem occ urs when i want the sum of all differences in the third column (rows are months dates). The autosumm formula wont work and the result is completely wrong. thanks miro Hi format the resulting cell with the custome format [hh]:mm -- Regards Frank Kabel Frankfurt, Germany axiom wrote: > Can anybody give me an advice > > Lets say i put i...

>>>Notice of Delayed Response Times<<<<
------=_NextPart_0001_48190A22 Content-Type: text/plain Content-Transfer-Encoding: 7bit Dear partners, Thank you for your patience during our recent service delays due to flooding in the Fargo area. We apologize for any inconvenience this may have caused and are working diligently to restore the service level you have become accustomed to receive with this service. We appreciate your patience as we work through posts and inquiries in the order they were received. Thank you for participating in the partner online technical communities! Jen Mehrer Microsoft Partner Newsgroups Suppo...

Converting Adobe PageMaker 6.5 files
Does anyone know if you can convert Adobe PageMaker version 6.5 files so that I can use MS Publisher 2007 to edit these files? I suspect the only way is the copy/paste solution. If you don't have PageMaker, maybe Adobe has help. There is a forum here that might have a solution: http://forums.adobe.com/thread/460491;jsessionid=39FEE1914BD4E0C9DF28733ADF71F413.node0?tstart=0 -- Mary Sauer http://msauer.mvps.org/ "SmithinSA" <SmithinSA@discussions.microsoft.com> wrote in message news:5CCB0500-7E15-4EDC-BC04-775B41F13D8F@microsoft.com... > Does anyone know if you can ...

number in system using arrivals and departures
Is there and easy way to figure out the total number of persons in a system if have access to the arrival and departure times. I have tried to use counts with if statements, but I keep getting the number 1. I know this is not correct. Is there any special macros or functions Thanks Hi you may provide some more details about your data. Could you post some example rows (plain text - no attachments please) and describe your expecte result -- Regards Frank Kabel Frankfurt, Germany megv wrote: > Is there and easy way to figure out the total number of persons in a > system if have access...

how would you calculate the number of hits to your website
Hi ,' can some one tell to me the answer of this question On a website, how would you calculate the number of hits to your website ...

Links won't work in emailed enewsletter, help!
Some links in my e-newletter are unactive when I email it. I've tried to secure the text box with an exact position by going to the text box format options and it won't even let me select this as an option. Any other suggestions? For hyperlinks to work the newsletter has to be HTML. Have you tried using a PDF for your newsletter? You can make the hyperlinks active in a PDF file. Do this search for a free converter. Not all of them have the feature you want. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "JulieCCD&qu...

columns changed to numbers instead of letters?
I noticed my excel clumns have changed from letters to numbers and forumlas now look like =RC[-1]*R[-3]C[6] I was trying to make the R[-3]C[6] static (using the $) but it errors. Thanks Mike In Excel Options uncheck "R1C1 Reference Style" Gord Dibben MS Excel MVP On Thu, 28 Oct 2010 20:25:30 -0400, Mike <no_please@not.com> wrote: >I noticed my excel clumns have changed from letters to numbers and >forumlas now look like =RC[-1]*R[-3]C[6] >I was trying to make the R[-3]C[6] static (using the $) but it errors. >Thanks >Mike ...

A way to increase a number in a cell by using the + key
Hi, i do a lot of data entry and am looking for a way to increase the number in a cell (or range of cells) so that i dont have to enter the new number in each time. I have columns that separate items and i use the sheet to show the total count for each item in it's row. eg Item 1 Item 2 Item 3 2 5 4 I'd like to use the + key only to increase the counts for each item. Did i make sense?? It's a Friday and my mind isn't quite up for a lot of detailed explanations, lol. Any assistance would be appreciated. Tim That would require VBA How far are you will...

Conditional Statements and Time Format
Hello, I have a spreadsheet where I need to calculate a range of time in a cell and display a value in another cell. Example is in cell F2 I have a time displayed of 15:34, and in the calculation window it displays as 3:34:00 PM. In Cell F3 I want to display one of three things, "1st shift", "2nd shift" or "3rd shift". Is it possible to use the conditional statements to give me the value of "1st shift" when cell F2 is between >= 07:00 and < 15:00? I can't find anything that speaks to getting thee range information from time. Thanks, Brian ...

reference number #2
--------------------------------- Where does the information in the "reference number" field in the transaction table come from? I see tranactions with reference numbers - the first part of which appears to be the year, month and day - but I can't figure out the second part. It is nothing that we have intentionally entered while ringing out the customer. Pls help. -Tracey ##-----------------------------------------------## Newsgroup Access Courtesy http://www.rockryno.com/ Tax and Accounting Software Forums Web and RSS access to your favorite newsgroup - microsoft.p...

Problem linking excel charts in word
I have linked an excel chart into a word document by pasting special as a link. It looks fine when I paste it. I close the application and when I open it again the chart is cut off on the left, and sometimes at the top. It's as if the chart itself is off the edge of the frame it is sitting in. The chart in excel is perfectly OK. I can edit the excel chart, and edits are updated in the word document when links are updated, but it doesn't change the position of teh chart, so it's still cutting off part of the chart. The chart is on it's own sheet rather than being i...

number changes to date ... how do I change
I am trying to do an "age" range: 1-9 10-19 20-29 Instead I get September 9, 2004 or October 19 I can't figure out how to change it. Please help! Thank you! kk Format->Cells->Number Change the type to Text, and it should keep everything the way you want it. If you don't specify a type, Excel takes a guess based on what you enter. Hope that helps. -Bob --- Message posted from http://www.ExcelForum.com/ ...

Add numbers accross columns after stripping away text
I have the following data in a spreadsheet: A1 B1 C1 D1 E1 F1 4.5f 6f 3.5f 3f 7.25f I need to be able to add the numbers together to give me 24.25, i.e. strip the fs away. The numbers will always be less than 10 and the there will only ever be .25 or.5 or .75 after the number (I don't know whether that is significant?). If anyone can show me how to do this I would be very grateful. I have been messing around with MID and FIND to no avail and then started thinking that SUMPRODUCT might have to get involved but it all got a bit m...

Random numbers, Canadian Zip Code style #2
Honestly, I don't even know where to begin -- Shocke ----------------------------------------------------------------------- Shocked's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1489 View this thread: http://www.excelforum.com/showthread.php?threadid=26523 ...

HELP!! How to formula the "sheet" link
Hi , I have a workbook contains 1+50 worksheet, I want to link each sheet number to sheet one, besides manually change the sheet # in the formula =SUM('sheet02'!$H$16:$H$21,'sheet02'!$H$26) =SUM('sheet03'!$H$16:$H$21,'sheet03'!$H$26) =SUM('sheet04'!$H$16:$H$21,'sheet04'!$H$26) =SUM('sheet05'!$H$16:$H$21,'sheet05'!$H$26) ..... up to sheet 50... Can someone help me to automatic it in anyway? Thank you very much!!!! Nicole Try a formula like the following: =SUM(INDIRECT("Sheet"&TEXT(ROW()-4+2,&...