calculating time

M

matilda

What is the formula to calculate time when i must account for the 24 hour time difference. I want my answer to show in hour and minute format. When i try to do this i keep getting some sort of date number in front of my answer.

Also, can i calculate a date and time together. Ex. I want to know how many hours from start time of Jan. 2, 04, 8 am to Jan 4, 04, 3 pm

thanks
 
T

Trevor Shuttleworth

Matilda

format the cell as Custom [hh]:mm

Regards

Trevor


matilda said:
What is the formula to calculate time when i must account for the 24 hour
Click to expand...
time difference. I want my answer to show in hour and minute format. When
i try to do this i keep getting some sort of date number in front of my
answer.
Also, can i calculate a date and time together. Ex. I want to know how
Click to expand...
many hours from start time of Jan. 2, 04, 8 am to Jan 4, 04, 3 pm.
 
M

matilda

thank you for your help- it worked.

Do you know if I can add date in with my time cells. ex. 1/4/04 10:30 am as start time and 1/6/04 3 pm as end time and have that calculate hours in answer cell.
 
T

Trevor Shuttleworth

Sure

A1: 01/04/2004 10:30:00
A2: 01/06/2004 15:00:00
=A2-A1 (formatted as [hh]:mm) = 1468:30

Regards

Trevor


matilda said:
thank you for your help- it worked.

Do you know if I can add date in with my time cells. ex. 1/4/04 10:30 am
Click to expand...
as start time and 1/6/04 3 pm as end time and have that calculate hours in
answer cell.
 
T

Trevor Shuttleworth

Matilda

Custom format: dd/mm/yyyy hh:mm AM/PM

22/01/2004 16:00:00 = 22/01/2004 04:00 PM


Regards

Trevor


MATILDA said:
AGAIN- Thank you for your help. How do I get it to show non-military
Click to expand...
time?
 
J

John Michael

Trevor:

I am trying to do the exact same thing, but only find the Custom [h]:m
format command in Excel v97 of Excel on my PC and v2001 on my Mac.

I do not find the format: Custom [hh]:mm.

Also, I use one cell for date, one for time in, one for time out. Whe
I use another cell to put the date and time together: =(A1&" "&A2
[where A1 is the date cell and A2 is the time cell] in the format
Custom m/d/yy h:mm, the date comes out as an integer, i.e., 02/14/04
36569.

Any suggestions?

Thanks in advance!

John Michae
 
N

Norman Harker

Hi John!

You have to type in the custom format strings yourself.

To add a date and time use:

=A1+A2

Dates are serial numbers representing the number of days from
31-Dec-1899. Times are decimal parts of 1 day. All that formatting
does is to control the way the numbers are represented. So to get the
date and time we just need to add and format the result. What you were
doing wrong was to think that the date and times were text.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
J

John Michael

Norman:

I see what you are saying about date and time storage in integer an
decimal.

However, I must be doing something wrong when you say, "You have t
type in the custom format strings yourself."

It's still not working for me. I'm getting #VALUE!, when the cel
formula is =C6+C7, where C6 is the date: 02/17/04 and set to the Custo
Format mm/dd/yyyy and D7 is the time, 9:00AM, set to the Time Forma
1:30 PM.

Basically I want to find the difference between in time and out time t
calculate how long a worker worked. Then subtract the new in time fo
the next date from the out time on the previous day to get the amoun
of hours they had in turnaround.

Any advice you give would be greatly appreciated!

BTW, Mum's an Aussie living in Sydney as well!

Cheers!

J
 
N

Norman Harker

Hi John!

Try entering your date as:

17-Feb-2004

It looks like a Regional options conflict with Excel telling you that
it has a problem with the 2nd of the 17th month of 2004

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top