Home |
Search |
Today's Posts |
#1
|
|||
|
|||
custom macro needed
I work for a company in Hertford now. We are an accident management company
as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers dont want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#2
|
|||
|
|||
custom macro needed
How do I find your example?
|
#3
|
|||
|
|||
custom macro needed
You don't need a macro for this, a simple formula will work
=IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#5
|
|||
|
|||
custom macro needed
In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#6
|
|||
|
|||
custom macro needed
firstly i dont have analysis toolpack which is probably why it didnt work and
even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#7
|
|||
|
|||
custom macro needed
The Analysis Toolpak is part of Excel, it just needs to be installed from
the Excel CD. Are you sure it is not installed, jut not loaded (InsertAddins, and check it) If your IT guys are not willing to do that (why not? - challenge them), then you could use =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7))- SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*( holidays<=(IF(B2="",TODAY(),B2))))) replace holidays with a range of dates for holidays, such as H1:H10, (even if you have none) -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... firstly i dont have analysis toolpack which is probably why it didnt work and even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#8
|
|||
|
|||
custom macro needed
thanks bob it was part of my excelk i just went to tools then addins and
ticked both boxes you need the normal and the vba ticked ill try this at work on monday thank you for your help. "Bob Phillips" wrote: The Analysis Toolpak is part of Excel, it just needs to be installed from the Excel CD. Are you sure it is not installed, jut not loaded (InsertAddins, and check it) If your IT guys are not willing to do that (why not? - challenge them), then you could use =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7))- SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*( holidays<=(IF(B2="",TODAY(),B2))))) replace holidays with a range of dates for holidays, such as H1:H10, (even if you have none) -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... firstly i dont have analysis toolpack which is probably why it didnt work and even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#9
|
|||
|
|||
custom macro needed
Bob
we apear to have hit a problem. i was unaware that our customer is only using office 2000 is there anyway we can adapt the formulae to just use the technologies available in excel 2000? "Stuart" wrote: thanks bob it was part of my excelk i just went to tools then addins and ticked both boxes you need the normal and the vba ticked ill try this at work on monday thank you for your help. "Bob Phillips" wrote: The Analysis Toolpak is part of Excel, it just needs to be installed from the Excel CD. Are you sure it is not installed, jut not loaded (InsertAddins, and check it) If your IT guys are not willing to do that (why not? - challenge them), then you could use =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7))- SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*( holidays<=(IF(B2="",TODAY(),B2))))) replace holidays with a range of dates for holidays, such as H1:H10, (even if you have none) -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... firstly i dont have analysis toolpack which is probably why it didnt work and even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#10
|
|||
|
|||
custom macro needed
So do I Stuart. What is it about 2000 that you think doesn't work with it.
-- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Bob we apear to have hit a problem. i was unaware that our customer is only using office 2000 is there anyway we can adapt the formulae to just use the technologies available in excel 2000? "Stuart" wrote: thanks bob it was part of my excelk i just went to tools then addins and ticked both boxes you need the normal and the vba ticked ill try this at work on monday thank you for your help. "Bob Phillips" wrote: The Analysis Toolpak is part of Excel, it just needs to be installed from the Excel CD. Are you sure it is not installed, jut not loaded (InsertAddins, and check it) If your IT guys are not willing to do that (why not? - challenge them), then you could use =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7))- SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*( holidays<=(IF(B2="",TODAY(),B2))))) replace holidays with a range of dates for holidays, such as H1:H10, (even if you have none) -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... firstly i dont have analysis toolpack which is probably why it didnt work and even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#11
|
|||
|
|||
custom macro needed
i dont know exactly but when the customer tried to enable the analysis
toolpack options he got a message saying that he needs excel 2003 so obviously the formulae cant work for excel 2000 so is there an alternative way of doing this that works for excel 2000? "Bob Phillips" wrote: So do I Stuart. What is it about 2000 that you think doesn't work with it. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Bob we apear to have hit a problem. i was unaware that our customer is only using office 2000 is there anyway we can adapt the formulae to just use the technologies available in excel 2000? "Stuart" wrote: thanks bob it was part of my excelk i just went to tools then addins and ticked both boxes you need the normal and the vba ticked ill try this at work on monday thank you for your help. "Bob Phillips" wrote: The Analysis Toolpak is part of Excel, it just needs to be installed from the Excel CD. Are you sure it is not installed, jut not loaded (InsertAddins, and check it) If your IT guys are not willing to do that (why not? - challenge them), then you could use =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7))- SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*( holidays<=(IF(B2="",TODAY(),B2))))) replace holidays with a range of dates for holidays, such as H1:H10, (even if you have none) -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... firstly i dont have analysis toolpack which is probably why it didnt work and even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#12
|
|||
|
|||
custom macro needed
Enabling the Toolpak cannot have anything to do with the formula, it just
doesn't figure into it when installing/enabling the Toolpak, There is something odd here that is impossible to debug from this distance. Did you try my non-ATP version? -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont know exactly but when the customer tried to enable the analysis toolpack options he got a message saying that he needs excel 2003 so obviously the formulae cant work for excel 2000 so is there an alternative way of doing this that works for excel 2000? "Bob Phillips" wrote: So do I Stuart. What is it about 2000 that you think doesn't work with it. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Bob we apear to have hit a problem. i was unaware that our customer is only using office 2000 is there anyway we can adapt the formulae to just use the technologies available in excel 2000? "Stuart" wrote: thanks bob it was part of my excelk i just went to tools then addins and ticked both boxes you need the normal and the vba ticked ill try this at work on monday thank you for your help. "Bob Phillips" wrote: The Analysis Toolpak is part of Excel, it just needs to be installed from the Excel CD. Are you sure it is not installed, jut not loaded (InsertAddins, and check it) If your IT guys are not willing to do that (why not? - challenge them), then you could use =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7))- SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*( holidays<=(IF(B2="",TODAY(),B2))))) replace holidays with a range of dates for holidays, such as H1:H10, (even if you have none) -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... firstly i dont have analysis toolpack which is probably why it didnt work and even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#13
|
|||
|
|||
custom macro needed
no as unfortunately it uses that holiday command and it is hard to predict
holidays especially as our bodyshops in scottland just take them whenever they feel like it as per scottish law. "Bob Phillips" wrote: Enabling the Toolpak cannot have anything to do with the formula, it just doesn't figure into it when installing/enabling the Toolpak, There is something odd here that is impossible to debug from this distance. Did you try my non-ATP version? -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont know exactly but when the customer tried to enable the analysis toolpack options he got a message saying that he needs excel 2003 so obviously the formulae cant work for excel 2000 so is there an alternative way of doing this that works for excel 2000? "Bob Phillips" wrote: So do I Stuart. What is it about 2000 that you think doesn't work with it. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Bob we apear to have hit a problem. i was unaware that our customer is only using office 2000 is there anyway we can adapt the formulae to just use the technologies available in excel 2000? "Stuart" wrote: thanks bob it was part of my excelk i just went to tools then addins and ticked both boxes you need the normal and the vba ticked ill try this at work on monday thank you for your help. "Bob Phillips" wrote: The Analysis Toolpak is part of Excel, it just needs to be installed from the Excel CD. Are you sure it is not installed, jut not loaded (InsertAddins, and check it) If your IT guys are not willing to do that (why not? - challenge them), then you could use =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7))- SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*( holidays<=(IF(B2="",TODAY(),B2))))) replace holidays with a range of dates for holidays, such as H1:H10, (even if you have none) -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... firstly i dont have analysis toolpack which is probably why it didnt work and even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#14
|
|||
|
|||
custom macro needed
Either create an empty range of holidays or strip it out
=IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7 -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... no as unfortunately it uses that holiday command and it is hard to predict holidays especially as our bodyshops in scottland just take them whenever they feel like it as per scottish law. "Bob Phillips" wrote: Enabling the Toolpak cannot have anything to do with the formula, it just doesn't figure into it when installing/enabling the Toolpak, There is something odd here that is impossible to debug from this distance. Did you try my non-ATP version? -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont know exactly but when the customer tried to enable the analysis toolpack options he got a message saying that he needs excel 2003 so obviously the formulae cant work for excel 2000 so is there an alternative way of doing this that works for excel 2000? "Bob Phillips" wrote: So do I Stuart. What is it about 2000 that you think doesn't work with it. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Bob we apear to have hit a problem. i was unaware that our customer is only using office 2000 is there anyway we can adapt the formulae to just use the technologies available in excel 2000? "Stuart" wrote: thanks bob it was part of my excelk i just went to tools then addins and ticked both boxes you need the normal and the vba ticked ill try this at work on monday thank you for your help. "Bob Phillips" wrote: The Analysis Toolpak is part of Excel, it just needs to be installed from the Excel CD. Are you sure it is not installed, jut not loaded (InsertAddins, and check it) If your IT guys are not willing to do that (why not? - challenge them), then you could use =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7))- SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*( holidays<=(IF(B2="",TODAY(),B2))))) replace holidays with a range of dates for holidays, such as H1:H10, (even if you have none) -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... firstly i dont have analysis toolpack which is probably why it didnt work and even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#15
|
|||
|
|||
custom macro needed
ok bob thanks for your help i will certainly give it a try however for now i
have been saving the excel sheets as webpages so the customer can still see the data as we see it without having to worry if he can run the addin hopefully that will solve problem if not ill try that formulae and see what happens thanks for all your help youve been great ill certainly remember you for any future queries. actualy while i am at it i do have another query. scenario: i have a column for drivable this column contains either a value of yes or no which i want selected from a dropdown list. i have a notification date a bookin date and an elapsed time. i want to set conditional formating on the elapsed time using the following conditions. if drivable: value of elapsed less than 6 use green background, if drivable and is 6 or greater show in orange and red if drivable and greater than 9. if non drivable: less than 3 background to be green if 3 or greater make it orange if 10 or greater then make it red. how do i? A) make all cells in drivable column use the dropdown list with preset values of my choice. B) get a formulae to make the formating changews i require based on the conditions specified. Stuart "Bob Phillips" wrote: Either create an empty range of holidays or strip it out =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7 -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... no as unfortunately it uses that holiday command and it is hard to predict holidays especially as our bodyshops in scottland just take them whenever they feel like it as per scottish law. "Bob Phillips" wrote: Enabling the Toolpak cannot have anything to do with the formula, it just doesn't figure into it when installing/enabling the Toolpak, There is something odd here that is impossible to debug from this distance. Did you try my non-ATP version? -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont know exactly but when the customer tried to enable the analysis toolpack options he got a message saying that he needs excel 2003 so obviously the formulae cant work for excel 2000 so is there an alternative way of doing this that works for excel 2000? "Bob Phillips" wrote: So do I Stuart. What is it about 2000 that you think doesn't work with it. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Bob we apear to have hit a problem. i was unaware that our customer is only using office 2000 is there anyway we can adapt the formulae to just use the technologies available in excel 2000? "Stuart" wrote: thanks bob it was part of my excelk i just went to tools then addins and ticked both boxes you need the normal and the vba ticked ill try this at work on monday thank you for your help. "Bob Phillips" wrote: The Analysis Toolpak is part of Excel, it just needs to be installed from the Excel CD. Are you sure it is not installed, jut not loaded (InsertAddins, and check it) If your IT guys are not willing to do that (why not? - challenge them), then you could use =IF(A2="","",SUMPRODUCT(INT(((IF(B2="",TODAY(),B2) )-WEEKDAY(A2+1-{2;3;4;5;6} )-A2+8)/7))- SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6},0))*(holidays=A2)*( holidays<=(IF(B2="",TODAY(),B2))))) replace holidays with a range of dates for holidays, such as H1:H10, (even if you have none) -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... firstly i dont have analysis toolpack which is probably why it didnt work and even if i knew where to get it from we arent allowed to install new software at our work and the customer wont want to install new software just to read there reports. is there any other way round this problem? "Bob Phillips" wrote: In your workbook, use the formula that I gave and in the first row, change onsite_date to A2, return_date to B2. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... i dont fully understand go to http://homepage.ntlworld.com/stuartb...e/Downtime.xls make the needed adjustments and send modified file to as this will be easier that way ill be able to see what you did. would you guys mind? "Bob Phillips" wrote: You don't need a macro for this, a simple formula will work =IF(onsite_date="","",IF(return_date="",NETWORKDAY S(onsite_date,TODAY()),NET WORKDAYS(onsite_date,return_date))) it uses NETWEORKDAYS which is part of the Analysis Toolpak, so that needs to be installed. -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers don't want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#16
|
|||
|
|||
custom macro needed
Bob
i need a formulae similar to the one you gave me for downtime. i have an estimate date and a date estimate was authorised, i want to know how long it took from the date of estimate to the date it was authorised but i want the weekends included. if authorised date not given o est date not given then return as blank. "Stuart" wrote: I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers dont want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
custom macro needed
bob
i need a specialist formulae. ok heres what i need. i have a date the accident was reported and the date the vehicle came onsite the difference between these two is put in a column called elapsed. according to my companys SLA's (service level agreements) that we have with our customers all vehicles that are drivable must be onsite within 5 days of when the accident was reported to us, all non drivable vehicles are to be recovered to the bodyshop within 2 days of notification. now then we want to use a traffic light based system in our customer reports, if the vehicles is recovered per SLA then it shows up green if it exceeds SLA criteria then it goes to orange, if it exceeds the 10 day threshold it must go to red indicating a serious problem and that the customer needs to get booked in quick. therefore i need a formulae to for my conditional formating that tests if the elapsed time is within SLA Specifications and take the appropriate formating action. could you please tell me what the formulae would be based on drivable values being in column G and elapsed times being in column J? in case it makes a difference the formulae used to calculate the elapse time is as follows. =IF(H2="","",IF(I2="",TODAY()-H2,I2-H2)) Stuart "Stuart" wrote: I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a onsit date is given but no returned date it will show how many days its been from onsite date to today's date. also if returned date is given but no onsite date it should return a blank value as our customers dont want to see "#VALUE" show up anywhere. lastly this formulae is limited in that our bodyshop's do not work on weekends yet the current formulae can't compensate for weekends is the any way to get the formulae to filter out weekends when doing its calculations? any help you can provide me with would be greatly appreciated. thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can T Get Macro To Run! | New Users to Excel | |||
Macro to omit blank cells needed | Excel Worksheet Functions | |||
Forlmula or macro needed | Excel Discussion (Misc queries) | |||
Copying a workbook with custom toolbar assigned to a macro | Excel Discussion (Misc queries) | |||
custom toolbar w/ macro | Excel Discussion (Misc queries) |