Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for hours
Hi, Thanks for letting me join your site. I've been looking for a site that will help me with excel. Thanks Here is my first question: I need to calculate the amount of hours i spent on a job. Example: I started at 2:17 AM and finished at 7:34 AM. on the sheet the first time is in cell 13D and the end time is in 13H. I need the total number of hours in cell 46A. What is the formula?? Thanks again. John -- John L ------------------------------------------------------------------------ John L's Profile: http://www.excelforum.com/member.php...o&userid=34774 View this thread: http://www.excelforum.com/showthread...hreadid=545361 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for hours
Hi
=13H-13D , or when your working time extends over midnight (but remains <24 hours) =13H-13D+(13H<13D) , and format as "hh:mm" - the result is displayed as time. To use the result in other formulas as number of hours, multiply it with 24 (1 hour in excel is same as 1/24) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "John L" wrote in message ... Hi, Thanks for letting me join your site. I've been looking for a site that will help me with excel. Thanks Here is my first question: I need to calculate the amount of hours i spent on a job. Example: I started at 2:17 AM and finished at 7:34 AM. on the sheet the first time is in cell 13D and the end time is in 13H. I need the total number of hours in cell 46A. What is the formula?? Thanks again. John -- John L ------------------------------------------------------------------------ John L's Profile: http://www.excelforum.com/member.php...o&userid=34774 View this thread: http://www.excelforum.com/showthread...hreadid=545361 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for hours
In A46:
=(H13-D13)*24 and format as number -- Regards, Dave "John L" wrote: Hi, Thanks for letting me join your site. I've been looking for a site that will help me with excel. Thanks Here is my first question: I need to calculate the amount of hours i spent on a job. Example: I started at 2:17 AM and finished at 7:34 AM. on the sheet the first time is in cell 13D and the end time is in 13H. I need the total number of hours in cell 46A. What is the formula?? Thanks again. John -- John L ------------------------------------------------------------------------ John L's Profile: http://www.excelforum.com/member.php...o&userid=34774 View this thread: http://www.excelforum.com/showthread...hreadid=545361 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for hours
Thanks David Billigmeier and Arvi Laanemets, I must be missing something. I applyed both formulas and it did not work. Let me explain what I'm trying to do. I'm a Fire/Police Captain. When we are called out on a call i must log the time of dispatch and the time we are finished with the call. The times will vary from PM and AM. Sometimes the calls start in the PM and finish in the AM. I need to log how much _time_ was used at the scene. What I'm looking for is the have it show X Hours and X mins. Example: Time at call: X Hours and X mins Thanks John L -- John L ------------------------------------------------------------------------ John L's Profile: http://www.excelforum.com/member.php...o&userid=34774 View this thread: http://www.excelforum.com/showthread...hreadid=545361 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for hours
John,
The formulas that David and Arvi gave you are correct, and do work. So in order to help you, we need to know what happened when 'it did not work.' What did happen? What result was displayed? Also, tell us how you type in the start and finish times (specifically, what characters do you type?). -- Regards, Fred "John L" wrote in message ... Thanks David Billigmeier and Arvi Laanemets, I must be missing something. I applyed both formulas and it did not work. Let me explain what I'm trying to do. I'm a Fire/Police Captain. When we are called out on a call i must log the time of dispatch and the time we are finished with the call. The times will vary from PM and AM. Sometimes the calls start in the PM and finish in the AM. I need to log how much _time_ was used at the scene. What I'm looking for is the have it show X Hours and X mins. Example: Time at call: X Hours and X mins Thanks John L -- John L ------------------------------------------------------------------------ John L's Profile: http://www.excelforum.com/member.php...o&userid=34774 View this thread: http://www.excelforum.com/showthread...hreadid=545361 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for hours
Thanks Again, I will be entering the times as...(example)..3:34 PM or 2:19 AM. Right now if i enter a time without the AM or PM it puts in the AM for any time i enter??? To enter the formulas that were given I click on the cell that I want the answer to appear in, I go to " insert functions ". The insert function box pops up. What do I pick from?? jOHN +-------------------------------------------------------------------+ |Filename: FIRE CALL REPORT.doc | |Download: http://www.excelforum.com/attachment.php?postid=4813 | +-------------------------------------------------------------------+ -- John L ------------------------------------------------------------------------ John L's Profile: http://www.excelforum.com/member.php...o&userid=34774 View this thread: http://www.excelforum.com/showthread...hreadid=545361 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for hours
Hi
As calculating time difference doesn't involve any functions, then you need to enter the formula directly into cell. Select the cell, and type there in something like this: =13H-13D+(13H<13D) (start with equal sign, adjust cell references accordingly your data) You have to ensure before, that the cell you'll type formula in isn't formatted as text. And after you entered the formula, you maybe have to format it - any valid time format will do. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "John L" wrote in message ... Thanks Again, I will be entering the times as...(example)..3:34 PM or 2:19 AM. Right now if i enter a time without the AM or PM it puts in the AM for any time i enter??? To enter the formulas that were given I click on the cell that I want the answer to appear in, I go to " insert functions ". The insert function box pops up. What do I pick from?? jOHN +-------------------------------------------------------------------+ |Filename: FIRE CALL REPORT.doc | |Download: http://www.excelforum.com/attachment.php?postid=4813 | +-------------------------------------------------------------------+ -- John L ------------------------------------------------------------------------ John L's Profile: http://www.excelforum.com/member.php...o&userid=34774 View this thread: http://www.excelforum.com/showthread...hreadid=545361 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for hours
Arvi Laanemets, Thanks very much, It worked. I entered what you said. Then i formatted the cell to CUSTOM/h:mm. Now when i enter the time of dispatched and finished i get " Hour:mm ". I can use this to let me know that the call lasted for (example) 3 hr and 32 mins. I'll have one more question. But first i have to see how to write it up so it can be understood. Thanks again and have a wonderful holiday weekend. John L -- John L ------------------------------------------------------------------------ John L's Profile: http://www.excelforum.com/member.php...o&userid=34774 View this thread: http://www.excelforum.com/showthread...hreadid=545361 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |