calculating time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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.
 
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.
 
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?
 
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
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman:

For some reason it just works on the Mac. Every cell format
correctly.

However, I'd like to utilize and IF statement:

=IF(C11>0.42,1,"")
or
=IF(C11>10,1,"")

Where C11 is the date+outtime minus the date+intime. I have this cel
formated [hh] so as to appear as a whole number.

So if an employee works over 10 hours a whole number "1" appears in th
If statement cell above, and I pay him for a lunch hour.

It's not working using >10 or >.42 or >0.42 where .42 is excel'
decimal equivalent of ten hours. (C11)

How am I not thinking this through correctly?

Thanks again in advance!

J
 
Hi John!

Excel time of 10:00 is 0.416666666666667

You could use:

=IF(C11>=10/24,1,"")
Or
=IF(C11>=TIME(10,0,0),1,"")


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
hi

pls help
i'm trying to calculate the differences between cells and i'm not sur
if i'm doing the right thing.
i have 4 rows
a1 creation date
21/11/2003 11:41

a2 receive date
21/11/2003 17:30

a3 repair completion and
22/11/2003 9:57

a4 time spent


i'd like to find how long it took to complete the repair between a3 an
a1. i also want it so that if there's a date in a2, it calculate
between a3 and a2 instead. all answer goes to a4.

is this correct?? i changed the time format on a4 to [hh]:mm
i then placed =a3-a2 in a4.. is this right?
and back to my original question, what if there's no data in a2, how d
i then automatically change it to look at a1?


ti
 
One way:

In A4:

=IF(A2="",A3-A1,A3-A2)

(I think I got it in the right order???)


noelf < said:
hi

pls help
i'm trying to calculate the differences between cells and i'm not sure
if i'm doing the right thing.
i have 4 rows
a1 creation date
21/11/2003 11:41

a2 receive date
21/11/2003 17:30

a3 repair completion and
22/11/2003 9:57

a4 time spent

i'd like to find how long it took to complete the repair between a3 and
a1. i also want it so that if there's a date in a2, it calculates
between a3 and a2 instead. all answer goes to a4.

is this correct?? i changed the time format on a4 to [hh]:mm
i then placed =a3-a2 in a4.. is this right?
and back to my original question, what if there's no data in a2, how do
i then automatically change it to look at a1?

tia
Click to expand...
 
Hi noelf!

Try:
=(A3-MAX(A1,A2))
Format [hh]:mm:ss

We can use MAX because if there is an A2 it will always be later than
A1 and if there isn't an A2 it will be treated as 0 and A1 will be the
latest.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
So do Buddy Holly and Elvis! Thanks for putting us in the same league.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman:

Those calcs work perfectly!

I was then able to create formulas that would calc the number of hour
if an employee didn't get a 10 hour turn around:

=IF((C8-B8)<10/24,10/24-(C8-B8),"")

And if they worked over 11 hours in one day:

=IF(A9>11/24,(B8-A8)-11/24,"")

However, sometimes I have to override my start time field for a trave
day with:

8T

If I do that, the rest of the formulas for the day return with :

#VALUE

How do I avoid this when I want to override the Start or Stop time
typically on the first day of the week they started working?

Any ideas?

Thanks again!

J
 
Hi John!

Perhaps the best way will be to have another cell in the row that
denotes a travel day.

You might get

=IF(D8<>"Travel
Day",IF((C8-B8)<10/24,10/24-(C8-B8),""),IF((C8-B8)<10/24,10/24-(C8-B8)
+2,""))

Or more efficiently:

=IF((C8-B8)<10/24,10/24-(C8-B8)+2*(D8="Travel Day),"")


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top