|
Time Calculations
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 |
Time Calculations
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 |
Time Calculations
=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 |
Time Calculations
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 |
Time Calculations
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 |
Time Calculations
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 |
Time Calculations
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 |
Time Calculations
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 |
Time Calculations
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 |
Time Calculations
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 |
Time Calculations
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 |
Time Calculations
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 |
| All times are GMT +1. The time now is 05:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com