How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value.

Suggestions?


"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

Darren,
It DOES work ... I have just tried it with your data and get
a result of 1:25 (hh:mm).

And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1
!!!!!!

"Darren" wrote:

Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

If you get this to work, could you email me a copy of the spreadsheet:


Thanks
Darren

"Toppers" wrote:

Darren,
It DOES work ... I have just tried it with your data and get
a result of 1:25 (hh:mm).

And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1
!!!!!!

"Darren" wrote:

Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

So toppers, if I had
A1 = 02/04/2007 10:30:00
B1 = 02/05/2007 10:30:00

C1=A1-B1

Will I get 24:00:00 for an answer? In your test, what happened to the dates?

"Toppers" wrote:

Darren,
It DOES work ... I have just tried it with your data and get
a result of 1:25 (hh:mm).

And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1
!!!!!!

"Darren" wrote:

Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

For hours = 24 format as [hh]:mm

"Darren" wrote:

If you get this to work, could you email me a copy of the spreadsheet:


Thanks
Darren

"Toppers" wrote:

Darren,
It DOES work ... I have just tried it with your data and get
a result of 1:25 (hh:mm).

And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1
!!!!!!

"Darren" wrote:

Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

so do I format A, B and C for [hh]:mm?

"Toppers" wrote:

For hours = 24 format as [hh]:mm

"Darren" wrote:

If you get this to work, could you email me a copy of the spreadsheet:


Thanks
Darren

"Toppers" wrote:

Darren,
It DOES work ... I have just tried it with your data and get
a result of 1:25 (hh:mm).

And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1
!!!!!!

"Darren" wrote:

Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

A B
c
1 02/04/2007 12:30:00 02/04/2007 12:56:03 =B1-A1

all 3 columns have been formated to Category Custom type [hh]:mm

Am I getting it all right? I still get #Value. I gotta start banging my
head on a wall :)

Darren


"Toppers" wrote:

For hours = 24 format as [hh]:mm

"Darren" wrote:

If you get this to work, could you email me a copy of the spreadsheet:


Thanks
Darren

"Toppers" wrote:

Darren,
It DOES work ... I have just tried it with your data and get
a result of 1:25 (hh:mm).

And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1
!!!!!!

"Darren" wrote:

Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

NO ...only C BUT even if they are formatted as C it will still wotk.

A & B are date/times CUSTOM= mm/dd/yyyy hh:mm (I assume US format)

Are you sure the fields are dates not text as if the fields are displayed as
you show in your posting they CANNOT be formatted as [HH]:MM as the date
would not be present?

If you want more help send sample to:

toppers at REMOVETHISjohntopley.fsnet.co.uk

"Darren" wrote:

A B
c
1 02/04/2007 12:30:00 02/04/2007 12:56:03 =B1-A1

all 3 columns have been formated to Category Custom type [hh]:mm

Am I getting it all right? I still get #Value. I gotta start banging my
head on a wall :)

Darren


"Toppers" wrote:

For hours = 24 format as [hh]:mm

"Darren" wrote:

If you get this to work, could you email me a copy of the spreadsheet:


Thanks
Darren

"Toppers" wrote:

Darren,
It DOES work ... I have just tried it with your data and get
a result of 1:25 (hh:mm).

And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1
!!!!!!

"Darren" wrote:

Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

If you are struggling to understand the replies in English in this group,
you may be better with a group in your own language?

Toppers suggested =B1-A1.
You told us that =A1-B1 didn't work.

For the example you gave, hh:mm will be fine as a format for the answer. If
you have a date & time span which will go beyond 24 hours, format the result
as [h]:mm
--
David Biddulph

"Darren" wrote in message
...
Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates
in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?

You are right, David, I did accidentally put A1-B1 in my question. Toppers
understood that was a mistake just by the question I had asked.

Anyways, Update: toppers continued to try to help me via email, and the
formula would work in his excel, but when I opened the page in my excel, the
formula would not work. I did figure out, though, why this was happening.

In my "Customize Regional Settings", I had my date separator as "-" rather
than "/". The "-" works great for my other database work, but apparently
excel does not like it.

so it is fixed now, thank you for your help, Topper.
As for you David, I did not like you insinuating that I do not understand
the english language. You need to think outside of the box, as this example
shows, the question seemed easy, but there was a quirk about getting it to
work for everyone.

"David Biddulph" wrote:

If you are struggling to understand the replies in English in this group,
you may be better with a group in your own language?

Toppers suggested =B1-A1.
You told us that =A1-B1 didn't work.

For the example you gave, hh:mm will be fine as a format for the answer. If
you have a date & time span which will go beyond 24 hours, format the result
as [h]:mm
--
David Biddulph

"Darren" wrote in message
...
Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates
in
the same box. like I said, I am trying to find the difference in the
following:

A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM

If you claim this works, then I guess I'm out of luck. I am running Excel
2003 SP2.

I don't think you guys are reading my entire posting. My cell contains
mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes,
hh:mm will work if I just had time.

Now, does anyone know if I can find the difference when A1 and B1 have the
data shown above?
Thank you.

"David Biddulph" wrote:

The suggestion has already been given by Toppers. Use that formula.
--
David Biddulph

"Darren" wrote in message
...
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM
When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried
formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting
#Value.

Suggestions?

"Toppers" wrote:

A1 start time
B1 end time
C1 =B1-A1

format as hh:mm

"Darren" wrote:

How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel:
2/9/2007 12:45:00 PM [start time]
2/9/2007 4:15:00 PM [end time]

How can I create a formula to give me the diffence in these times?