Setting up spreadsheet to calculate time
 

Can you tell me the best way to set up a spreadsheet in order to calculate
time, and the formula to do it for Excel 2003?
I have a start date and time and an end date and time and need to know the
number of hours and minutes between those two times.
I have a spreadsheet that someone else made and it just isn't working. I've
tried several formulas but can't get them to work and I'm pulling my hair
out. I'll go blind if I have to go down this spreadsheet and figure these
times out individually!!
Thanks :)
Meenie

Setting up spreadsheet to calculate time
 

Meenie,

I have a spreadsheet that someone else made and it just isn't working

Is just about as unhelpful as it gets, what isn't working, what error do you
get?

Getting the difference between 2 properly formatted dates/times is achieved
with subtraction. So

a1=01/01/2007 08:00:00
a2= 02/01/2008 08:10:00

Both formatted with dd/mm/yyyy hh:mm

In A3 enter =A2-A1
format as [hh]:mm and you will get your answer in hours and minutes.

Mike

"Meenie" wrote:

Can you tell me the best way to set up a spreadsheet in order to calculate
time, and the formula to do it for Excel 2003?
I have a start date and time and an end date and time and need to know the
number of hours and minutes between those two times.
I have a spreadsheet that someone else made and it just isn't working. I've
tried several formulas but can't get them to work and I'm pulling my hair
out. I'll go blind if I have to go down this spreadsheet and figure these
times out individually!!
Thanks :)
Meenie

Setting up spreadsheet to calculate time
 

Sorry Mike, I tried it so many ways and got so many results. :)
But your way works!! Thanks so much. Now I have to change the spreadsheet
<sigh but that's ok, lol

"Mike H" wrote:

Meenie,

I have a spreadsheet that someone else made and it just isn't working

Is just about as unhelpful as it gets, what isn't working, what error do you
get?

Getting the difference between 2 properly formatted dates/times is achieved
with subtraction. So

a1=01/01/2007 08:00:00
a2= 02/01/2008 08:10:00

Both formatted with dd/mm/yyyy hh:mm

In A3 enter =A2-A1
format as [hh]:mm and you will get your answer in hours and minutes.

Mike

"Meenie" wrote:

Can you tell me the best way to set up a spreadsheet in order to calculate
time, and the formula to do it for Excel 2003?
I have a start date and time and an end date and time and need to know the
number of hours and minutes between those two times.
I have a spreadsheet that someone else made and it just isn't working. I've
tried several formulas but can't get them to work and I'm pulling my hair
out. I'll go blind if I have to go down this spreadsheet and figure these
times out individually!!
Thanks :)
Meenie

oops problem Setting up spreadsheet to calculate time
 

UH OH. It works for everything except midnight.
If I use 12:00:00 am (or 00:00:00 am) it adds an extra 24 hrs to the answer?!?

"Meenie" wrote:

Sorry Mike, I tried it so many ways and got so many results. :)
But your way works!! Thanks so much. Now I have to change the spreadsheet
<sigh but that's ok, lol

"Mike H" wrote:

Meenie,

I have a spreadsheet that someone else made and it just isn't working

Is just about as unhelpful as it gets, what isn't working, what error do you
get?

Getting the difference between 2 properly formatted dates/times is achieved
with subtraction. So

a1=01/01/2007 08:00:00
a2= 02/01/2008 08:10:00

Both formatted with dd/mm/yyyy hh:mm

In A3 enter =A2-A1
format as [hh]:mm and you will get your answer in hours and minutes.

Mike

"Meenie" wrote:

Can you tell me the best way to set up a spreadsheet in order to calculate
time, and the formula to do it for Excel 2003?
I have a start date and time and an end date and time and need to know the
number of hours and minutes between those two times.
I have a spreadsheet that someone else made and it just isn't working. I've
tried several formulas but can't get them to work and I'm pulling my hair
out. I'll go blind if I have to go down this spreadsheet and figure these
times out individually!!
Thanks :)
Meenie

oops problem Setting up spreadsheet to calculate time
 

Here's what I have
4/30/2008 12:00:00 am (also tried 0:00:00 am)
5/1/2008 6:00:00 pm
answer I'm getting: 42!
But if I change the first column to 4/30/2008 11:59:00 pm I get 18:01.

"Meenie" wrote:

UH OH. It works for everything except midnight.
If I use 12:00:00 am (or 00:00:00 am) it adds an extra 24 hrs to the answer?!?

"Meenie" wrote:

Sorry Mike, I tried it so many ways and got so many results. :)
But your way works!! Thanks so much. Now I have to change the spreadsheet
<sigh but that's ok, lol

"Mike H" wrote:

Meenie,

I have a spreadsheet that someone else made and it just isn't working

Is just about as unhelpful as it gets, what isn't working, what error do you
get?

Getting the difference between 2 properly formatted dates/times is achieved
with subtraction. So

a1=01/01/2007 08:00:00
a2= 02/01/2008 08:10:00

Both formatted with dd/mm/yyyy hh:mm

In A3 enter =A2-A1
format as [hh]:mm and you will get your answer in hours and minutes.

Mike

"Meenie" wrote:

Can you tell me the best way to set up a spreadsheet in order to calculate
time, and the formula to do it for Excel 2003?
I have a start date and time and an end date and time and need to know the
number of hours and minutes between those two times.
I have a spreadsheet that someone else made and it just isn't working. I've
tried several formulas but can't get them to work and I'm pulling my hair
out. I'll go blind if I have to go down this spreadsheet and figure these
times out individually!!
Thanks :)
Meenie

oops problem Setting up spreadsheet to calculate time
 

(A1-B1)*1440 as a number for minuits ;)
(A1-B1)*1440/60 as a number with 2 decimal places for hours ;)

"Meenie" wrote:

Here's what I have
4/30/2008 12:00:00 am (also tried 0:00:00 am)
5/1/2008 6:00:00 pm
answer I'm getting: 42!
But if I change the first column to 4/30/2008 11:59:00 pm I get 18:01.

"Meenie" wrote:

UH OH. It works for everything except midnight.
If I use 12:00:00 am (or 00:00:00 am) it adds an extra 24 hrs to the answer?!?

"Meenie" wrote:

Sorry Mike, I tried it so many ways and got so many results. :)
But your way works!! Thanks so much. Now I have to change the spreadsheet
<sigh but that's ok, lol

"Mike H" wrote:

Meenie,

I have a spreadsheet that someone else made and it just isn't working

Is just about as unhelpful as it gets, what isn't working, what error do you
get?

Getting the difference between 2 properly formatted dates/times is achieved
with subtraction. So

a1=01/01/2007 08:00:00
a2= 02/01/2008 08:10:00

Both formatted with dd/mm/yyyy hh:mm

In A3 enter =A2-A1
format as [hh]:mm and you will get your answer in hours and minutes.

Mike

"Meenie" wrote:

Can you tell me the best way to set up a spreadsheet in order to calculate
time, and the formula to do it for Excel 2003?
I have a start date and time and an end date and time and need to know the
number of hours and minutes between those two times.
I have a spreadsheet that someone else made and it just isn't working. I've
tried several formulas but can't get them to work and I'm pulling my hair
out. I'll go blind if I have to go down this spreadsheet and figure these
times out individually!!
Thanks :)
Meenie