Function for hours

[John L]

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

[Arvi Laanemets]

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<at>tarkon.ee )


"John L" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[David Billigmeier]

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

[John L]

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 X mins

Thanks John L

[Fred Smith]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[John L]

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

[Arvi Laanemets]

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<at>tarkon.ee )



"John L" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[John L]

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