Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days per month for calculating storage days
Hello there,
can anyone help me solve the error I get when I enter the below given formula ? Any help is much appreciated. Thanks, Bart ******************************** Bernie, first of all, thanks for your efforts in trying to find a solution ! When I use the formula though, it comes up with an error. See hxxp:// members.home.nl/hoenb/SNAG-0024.jpg for details on the error. Many thanks in advance for your tip on solving the error. Bart On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org wrote: - Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Bart, This solution assumes the following: Your chasis numbers are in column A, starting in row 3, your entry dates are in column B, starting in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column D, you have the dates for the first of the month, for the time period that you are interested in: Aug 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you like... Then in cell D3, enter the formula =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3=E$ 2),E$2-D$2,IF(AND($B3<=E$2,$C3=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3=D$2,$C3<=E$2),$C3-$B3,0))))) Watch the line wrapping.... Copy that formula down as far as you need (to match your data rows), then copy across to match the dates in row 3. You will get a table of the days for each month for each chasis number. HTH, Bernie MS Excel MVP "Bart" wrote in message ups.com... Hi, I have a sheet with collums for entry date and exit date used in a car- compound. I also have a collumn for each month of the year. What I am trying to do is let excel (VBA) calculate how many days a car was on the compound in every month. This is a monthly/yearly ocurring item to check the invoicing. What I'd like to do is to pull data from our database into the excel sheet, just the chassis number, entry date and exit date. I would like to paste this in my excel, and then be presented with the amount of days a car was on the compound in each month. Is there any way to this ? I know that if it was a full month, it's easy to do, but let's say it has an entry on 13.01.06 and an exit on 20.10.06 , then it becomes more complicated. Also the calculation has to be performed on several thousand cars at once... Any help ?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days per month for calculating storage days
Do you have a continental version of Excel? If so, try
=IF(AND($B3<D$2,$C3<D$2);0; IF(AND($B3<=D$2,$C3=E$2);E$2-D$2;IF(AND($B3<=E$2;$C3=E$2);E$2-$B3; IF(AND($B3<=D$2,$C3<=E$2);$C3-D$2+1;IF(AND($B3=D$2;$C3<=E$2);$C3-$B3;0))))) If that doesn't work, give an example of a simple IF formula that does work for you, and your language, it may need different functions. Also make sure that D2, E2, etc are start of month dates as Bern ie assumed, not text. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bart" wrote in message ups.com... Hello there, can anyone help me solve the error I get when I enter the below given formula ? Any help is much appreciated. Thanks, Bart ******************************** Bernie, first of all, thanks for your efforts in trying to find a solution ! When I use the formula though, it comes up with an error. See hxxp:// members.home.nl/hoenb/SNAG-0024.jpg for details on the error. Many thanks in advance for your tip on solving the error. Bart On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org wrote: - Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Bart, This solution assumes the following: Your chasis numbers are in column A, starting in row 3, your entry dates are in column B, starting in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column D, you have the dates for the first of the month, for the time period that you are interested in: Aug 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you like... Then in cell D3, enter the formula =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3=E$ 2),E$2-D$2,IF(AND($B3<=E$2 ,$C3=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3=D$2,$C3<= E$2),$C3-$B3,0))))) Watch the line wrapping.... Copy that formula down as far as you need (to match your data rows), then copy across to match the dates in row 3. You will get a table of the days for each month for each chasis number. HTH, Bernie MS Excel MVP "Bart" wrote in message ups.com... Hi, I have a sheet with collums for entry date and exit date used in a car- compound. I also have a collumn for each month of the year. What I am trying to do is let excel (VBA) calculate how many days a car was on the compound in every month. This is a monthly/yearly ocurring item to check the invoicing. What I'd like to do is to pull data from our database into the excel sheet, just the chassis number, entry date and exit date. I would like to paste this in my excel, and then be presented with the amount of days a car was on the compound in each month. Is there any way to this ? I know that if it was a full month, it's easy to do, but let's say it has an entry on 13.01.06 and an exit on 20.10.06 , then it becomes more complicated. Also the calculation has to be performed on several thousand cars at once... Any help ?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days per month for calculating storage days
Bob,
I use a Dutch version of excel, but your variant also comes up with the same error. Date is ( for January ) 01-01-2006 formatted as mmmm- y so visible as january-06 Simple example of working if function : =ALS(ISGETAL(C3);C3*10;"ERROR") [ if it's a number, calculate, else display the text ERROR ] Thanks for feedback. Bart On 31 jan, 14:30, "Bob Phillips" wrote: Do you have a continental version of Excel? If so, try =IF(AND($B3<D$2,$C3<D$2);0; IF(AND($B3<=D$2,$C3=E$2);E$2-D$2;IF(AND($B3<=E$2;$C3=E$2);E$2-$B3; IF(AND($B3<=D$2,$C3<=E$2);$C3-D$2+1;IF(AND($B3=D$2;$C3<=E$2);$C3-$B3;0))))) If that doesn't work, give an example of a simple IF formula that does work for you, and your language, it may need different functions. Also make sure that D2, E2, etc are start of month dates as Bern ie assumed, not text. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bart" wrote in message ups.com... Hello there, can anyone help me solve the error I get when I enter the below given formula ? Any help is much appreciated. Thanks, Bart ******************************** Bernie, first of all, thanks for your efforts in trying to find a solution ! When I use the formula though, it comes up with an error. See hxxp:// members.home.nl/hoenb/SNAG-0024.jpg for details on the error. Many thanks in advance for your tip on solving the error. Bart On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org wrote: - Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Bart, This solution assumes the following: Your chasis numbers are in column A, starting in row 3, your entry dates are in column B, starting in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column D, you have the dates for the first of the month, for the time period that you are interested in: Aug 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you like... Then in cell D3, enter the formula =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3=E$ 2),E$2-D$2,IF(AND($B3<=E$2 ,$C3=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3=D$2,$C3<= E$2),$C3-$B3,0))))) Watch the line wrapping.... Copy that formula down as far as you need (to match your data rows), then copy across to match the dates in row 3. You will get a table of the days for each month for each chasis number. HTH, Bernie MS Excel MVP "Bart" wrote in message oups.com... Hi, I have a sheet with collums for entry date and exit date used in a car- compound. I also have a collumn for each month of the year. What I am trying to do is let excel (VBA) calculate how many days a car was on the compound in every month. This is a monthly/yearly ocurring item to check the invoicing. What I'd like to do is to pull data from our database into the excel sheet, just the chassis number, entry date and exit date. I would like to paste this in my excel, and then be presented with the amount of days a car was on the compound in each month. Is there any way to this ? I know that if it was a full month, it's easy to do, but let's say it has an entry on 13.01.06 and an exit on 20.10.06 , then it becomes more complicated. Also the calculation has to be performed on several thousand cars at once... Any help ?? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Days per month for calculating storage days
So try this one Bart
=ALS(EN($B3<D$2,$C3<D$2);0; ALS(EN($B3<=D$2,$C3=E$2);E$2-D$2;ALS(EN($B3<=E$2;$C3=E$2);E$2-$B3; ALS(EN($B3<=D$2,$C3<=E$2);$C3-D$2+1;ALS(EN($B3=D$2;$C3<=E$2);$C3-$B3;0))))) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bart" wrote in message oups.com... Bob, I use a Dutch version of excel, but your variant also comes up with the same error. Date is ( for January ) 01-01-2006 formatted as mmmm- y so visible as january-06 Simple example of working if function : =ALS(ISGETAL(C3);C3*10;"ERROR") [ if it's a number, calculate, else display the text ERROR ] Thanks for feedback. Bart On 31 jan, 14:30, "Bob Phillips" wrote: Do you have a continental version of Excel? If so, try =IF(AND($B3<D$2,$C3<D$2);0; IF(AND($B3<=D$2,$C3=E$2);E$2-D$2;IF(AND($B3<=E$2;$C3=E$2);E$2-$B3; IF(AND($B3<=D$2,$C3<=E$2);$C3-D$2+1;IF(AND($B3=D$2;$C3<=E$2);$C3-$B3;0))))) If that doesn't work, give an example of a simple IF formula that does work for you, and your language, it may need different functions. Also make sure that D2, E2, etc are start of month dates as Bern ie assumed, not text. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bart" wrote in message ups.com... Hello there, can anyone help me solve the error I get when I enter the below given formula ? Any help is much appreciated. Thanks, Bart ******************************** Bernie, first of all, thanks for your efforts in trying to find a solution ! When I use the formula though, it comes up with an error. See hxxp:// members.home.nl/hoenb/SNAG-0024.jpg for details on the error. Many thanks in advance for your tip on solving the error. Bart On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org wrote: - Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Bart, This solution assumes the following: Your chasis numbers are in column A, starting in row 3, your entry dates are in column B, starting in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column D, you have the dates for the first of the month, for the time period that you are interested in: Aug 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you like... Then in cell D3, enter the formula =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3=E$ 2),E$2-D$2,IF(AND($B3<=E$2 ,$C3=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3=D$2,$C3<= E$2),$C3-$B3,0))))) Watch the line wrapping.... Copy that formula down as far as you need (to match your data rows), then copy across to match the dates in row 3. You will get a table of the days for each month for each chasis number. HTH, Bernie MS Excel MVP "Bart" wrote in message oups.com... Hi, I have a sheet with collums for entry date and exit date used in a car- compound. I also have a collumn for each month of the year. What I am trying to do is let excel (VBA) calculate how many days a car was on the compound in every month. This is a monthly/yearly ocurring item to check the invoicing. What I'd like to do is to pull data from our database into the excel sheet, just the chassis number, entry date and exit date. I would like to paste this in my excel, and then be presented with the amount of days a car was on the compound in each month. Is there any way to this ? I know that if it was a full month, it's easy to do, but let's say it has an entry on 13.01.06 and an exit on 20.10.06 , then it becomes more complicated. Also the calculation has to be performed on several thousand cars at once... Any help ?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Month End Projection formula? | Excel Worksheet Functions | |||
Fomula for number of days on each month from a date range | Excel Discussion (Misc queries) | |||
Number of Days in the Month | Excel Discussion (Misc queries) | |||
Calculate Days in a Month | Excel Discussion (Misc queries) | |||
Summarizing of columns for different days of month | Excel Discussion (Misc queries) |