I have an instance where we are using excel to calculate work times however
on some calculations an error appears. I am told that excel does not calcuate
or recognize negative time differences. The formula that I am currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

Hi Mark

It should be B1-A1, but in order to cope with times crossing the 24 hour
barrier, use
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Mark" wrote in message
...
I have an instance where we are using excel to calculate work times
however
on some calculations an error appears. I am told that excel does not
calcuate
or recognize negative time differences. The formula that I am currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

=B1-A1+(A1B1)


"Mark" wrote:

I have an instance where we are using excel to calculate work times however
on some calculations an error appears. I am told that excel does not calcuate
or recognize negative time differences. The formula that I am currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

Thanks a million!!!
--
Mark


"Roger Govier" wrote:

Hi Mark

It should be B1-A1, but in order to cope with times crossing the 24 hour
barrier, use
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Mark" wrote in message
...
I have an instance where we are using excel to calculate work times
however
on some calculations an error appears. I am told that excel does not
calcuate
or recognize negative time differences. The formula that I am currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

Thanks a million!!!
--
Mark


"Teethless mama" wrote:

=B1-A1+(A1B1)


"Mark" wrote:

I have an instance where we are using excel to calculate work times however
on some calculations an error appears. I am told that excel does not calcuate
or recognize negative time differences. The formula that I am currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

thanks for the info Roger, however i have arrived at another problem whe

6:00 AM - 6:00 AM = 00:00:00

Even if i put a date to it the result is the same. How can i get this to
recognize or bring me back a result of 24 hrs?

Thanks in advance for your assistance.

Best regards,
--
Mark


"Roger Govier" wrote:

Hi Mark

It should be B1-A1, but in order to cope with times crossing the 24 hour
barrier, use
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Mark" wrote in message
...
I have an instance where we are using excel to calculate work times
however
on some calculations an error appears. I am told that excel does not
calcuate
or recognize negative time differences. The formula that I am currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

For 24 hours and above you would need the date as well then use custom
format

[h]:mm

--


Regards,


Peo Sjoblom

"Mark" wrote in message
...
thanks for the info Roger, however i have arrived at another problem
whe

6:00 AM - 6:00 AM = 00:00:00

Even if i put a date to it the result is the same. How can i get this to
recognize or bring me back a result of 24 hrs?

Thanks in advance for your assistance.

Best regards,
--
Mark


"Roger Govier" wrote:

Hi Mark

It should be B1-A1, but in order to cope with times crossing the 24 hour
barrier, use
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Mark" wrote in message
...
I have an instance where we are using excel to calculate work times
however
on some calculations an error appears. I am told that excel does not
calcuate
or recognize negative time differences. The formula that I am
currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

Peo, tried what you suggested hwvr, still arriving with the same result of a
0 figu


10/01/2008 06:00 10/02/2008 06:00 00:00


Where the formula is =MOD(B1-A1;1) and the answer format is [hh]:mm.

Can you sugguest anything that will make the result equal 24:00?

Thanks in advance for your assistance.

Best regards,


--
Mark


"Peo Sjoblom" wrote:

For 24 hours and above you would need the date as well then use custom
format

[h]:mm

--


Regards,


Peo Sjoblom

"Mark" wrote in message
...
thanks for the info Roger, however i have arrived at another problem
whe

6:00 AM - 6:00 AM = 00:00:00

Even if i put a date to it the result is the same. How can i get this to
recognize or bring me back a result of 24 hrs?

Thanks in advance for your assistance.

Best regards,
--
Mark


"Roger Govier" wrote:

Hi Mark

It should be B1-A1, but in order to cope with times crossing the 24 hour
barrier, use
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Mark" wrote in message
...
I have an instance where we are using excel to calculate work times
however
on some calculations an error appears. I am told that excel does not
calcuate
or recognize negative time differences. The formula that I am
currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

You could use =IF(MOD(B1-A1;1)=0;1;MOD(B1-A1;1))
but why have you used MOD() in the first place, when you have date and time
in the cell?
Why didn't you just use =B1-A1 ?
The suggestion of MOD(B1-A1,1) was for when you DIDN'T have dates and just
had times.

If you use answer 49 but apply it to question 57, it isn't surprising if you
get unexpected results.
--
David Biddulph

"Mark" wrote in message
...
Peo, tried what you suggested hwvr, still arriving with the same result of
a
0 figu


10/01/2008 06:00 10/02/2008 06:00 00:00


Where the formula is =MOD(B1-A1;1) and the answer format is [hh]:mm.

Can you sugguest anything that will make the result equal 24:00?

Thanks in advance for your assistance.

Best regards,


--
Mark


"Peo Sjoblom" wrote:

For 24 hours and above you would need the date as well then use custom
format

[h]:mm

--


Regards,


Peo Sjoblom

"Mark" wrote in message
...
thanks for the info Roger, however i have arrived at another problem
whe

6:00 AM - 6:00 AM = 00:00:00

Even if i put a date to it the result is the same. How can i get this
to
recognize or bring me back a result of 24 hrs?

Thanks in advance for your assistance.

Best regards,
--
Mark


"Roger Govier" wrote:

Hi Mark

It should be B1-A1, but in order to cope with times crossing the 24
hour
barrier, use
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Mark" wrote in message
...
I have an instance where we are using excel to calculate work times
however
on some calculations an error appears. I am told that excel does not
calcuate
or recognize negative time differences. The formula that I am
currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

Thank you David for the info...you are correct as I used MOD() when we didn't
have dates just times. The orginal idea was to have the spreadsheet read:

Date Description Time In Time Out

Now our times do run for 24hrs and sometimes over where a time spent on a
job maybe 24:45. How do I get Excel to recognise this as with your formula
the result would read 00:45?

Any assistance or suggestions would be appreicated.

Brgds,
--
Mark


"David Biddulph" wrote:

You could use =IF(MOD(B1-A1;1)=0;1;MOD(B1-A1;1))
but why have you used MOD() in the first place, when you have date and time
in the cell?
Why didn't you just use =B1-A1 ?
The suggestion of MOD(B1-A1,1) was for when you DIDN'T have dates and just
had times.

If you use answer 49 but apply it to question 57, it isn't surprising if you
get unexpected results.
--
David Biddulph

"Mark" wrote in message
...
Peo, tried what you suggested hwvr, still arriving with the same result of
a
0 figu


10/01/2008 06:00 10/02/2008 06:00 00:00


Where the formula is =MOD(B1-A1;1) and the answer format is [hh]:mm.

Can you sugguest anything that will make the result equal 24:00?

Thanks in advance for your assistance.

Best regards,


--
Mark


"Peo Sjoblom" wrote:

For 24 hours and above you would need the date as well then use custom
format

[h]:mm

--


Regards,


Peo Sjoblom

"Mark" wrote in message
...
thanks for the info Roger, however i have arrived at another problem
whe

6:00 AM - 6:00 AM = 00:00:00

Even if i put a date to it the result is the same. How can i get this
to
recognize or bring me back a result of 24 hrs?

Thanks in advance for your assistance.

Best regards,
--
Mark


"Roger Govier" wrote:

Hi Mark

It should be B1-A1, but in order to cope with times crossing the 24
hour
barrier, use
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Mark" wrote in message
...
I have an instance where we are using excel to calculate work times
however
on some calculations an error appears. I am told that excel does not
calcuate
or recognize negative time differences. The formula that I am
currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

You really do need to read the advice you've had.

"For 24 hours and above you would need the date as well then use custom
format [h]:mm"
and
"Why didn't you just use =B1-A1 ?"
--
David Biddulph

"Mark" wrote in message
...
Thank you David for the info...you are correct as I used MOD() when we
didn't
have dates just times. The orginal idea was to have the spreadsheet read:

Date Description Time In Time Out

Now our times do run for 24hrs and sometimes over where a time spent on a
job maybe 24:45. How do I get Excel to recognise this as with your formula
the result would read 00:45?

Any assistance or suggestions would be appreicated.

Brgds,
--
Mark


"David Biddulph" wrote:

You could use =IF(MOD(B1-A1;1)=0;1;MOD(B1-A1;1))
but why have you used MOD() in the first place, when you have date and
time
in the cell?
Why didn't you just use =B1-A1 ?
The suggestion of MOD(B1-A1,1) was for when you DIDN'T have dates and
just
had times.

If you use answer 49 but apply it to question 57, it isn't surprising if
you
get unexpected results.
--
David Biddulph

"Mark" wrote in message
...
Peo, tried what you suggested hwvr, still arriving with the same result
of
a
0 figu


10/01/2008 06:00 10/02/2008 06:00 00:00


Where the formula is =MOD(B1-A1;1) and the answer format is [hh]:mm.

Can you sugguest anything that will make the result equal 24:00?

Thanks in advance for your assistance.

Best regards,


--
Mark


"Peo Sjoblom" wrote:

For 24 hours and above you would need the date as well then use custom
format

[h]:mm

--


Regards,


Peo Sjoblom

"Mark" wrote in message
...
thanks for the info Roger, however i have arrived at another problem
whe

6:00 AM - 6:00 AM = 00:00:00

Even if i put a date to it the result is the same. How can i get
this
to
recognize or bring me back a result of 24 hrs?

Thanks in advance for your assistance.

Best regards,
--
Mark


"Roger Govier" wrote:

Hi Mark

It should be B1-A1, but in order to cope with times crossing the 24
hour
barrier, use
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Mark" wrote in message
...
I have an instance where we are using excel to calculate work
times
however
on some calculations an error appears. I am told that excel does
not
calcuate
or recognize negative time differences. The formula that I am
currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark

Thank you sir will do.

Brgds,
--
Mark


"David Biddulph" wrote:

You really do need to read the advice you've had.

"For 24 hours and above you would need the date as well then use custom
format [h]:mm"
and
"Why didn't you just use =B1-A1 ?"
--
David Biddulph

"Mark" wrote in message
...
Thank you David for the info...you are correct as I used MOD() when we
didn't
have dates just times. The orginal idea was to have the spreadsheet read:

Date Description Time In Time Out

Now our times do run for 24hrs and sometimes over where a time spent on a
job maybe 24:45. How do I get Excel to recognise this as with your formula
the result would read 00:45?

Any assistance or suggestions would be appreicated.

Brgds,
--
Mark


"David Biddulph" wrote:

You could use =IF(MOD(B1-A1;1)=0;1;MOD(B1-A1;1))
but why have you used MOD() in the first place, when you have date and
time
in the cell?
Why didn't you just use =B1-A1 ?
The suggestion of MOD(B1-A1,1) was for when you DIDN'T have dates and
just
had times.

If you use answer 49 but apply it to question 57, it isn't surprising if
you
get unexpected results.
--
David Biddulph

"Mark" wrote in message
...
Peo, tried what you suggested hwvr, still arriving with the same result
of
a
0 figu


10/01/2008 06:00 10/02/2008 06:00 00:00


Where the formula is =MOD(B1-A1;1) and the answer format is [hh]:mm.

Can you sugguest anything that will make the result equal 24:00?

Thanks in advance for your assistance.

Best regards,


--
Mark


"Peo Sjoblom" wrote:

For 24 hours and above you would need the date as well then use custom
format

[h]:mm

--


Regards,


Peo Sjoblom

"Mark" wrote in message
...
thanks for the info Roger, however i have arrived at another problem
whe

6:00 AM - 6:00 AM = 00:00:00

Even if i put a date to it the result is the same. How can i get
this
to
recognize or bring me back a result of 24 hrs?

Thanks in advance for your assistance.

Best regards,
--
Mark


"Roger Govier" wrote:

Hi Mark

It should be B1-A1, but in order to cope with times crossing the 24
hour
barrier, use
=MOD(B1-A1,1)

--
Regards
Roger Govier

"Mark" wrote in message
...
I have an instance where we are using excel to calculate work
times
however
on some calculations an error appears. I am told that excel does
not
calcuate
or recognize negative time differences. The formula that I am
currently
using is A1-B1. Is there any way to over come this?

Time In Time Out Hours Worked
6:00 AM 6:30 PM ####
6:30 PM 6:00 AM 12:30

Thanks in advance for your help.
--
Mark