Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting days in month
How can I separate days into different months and count them ? If I have a date of arrival and a date of departu 17/05/2006 to 22/06/2006 how can I count the numberof days in May and the number of days in June and have them separated ? Thanks in advance. -- jamesjohn ------------------------------------------------------------------------ jamesjohn's Profile: http://www.excelforum.com/member.php...fo&userid=9692 View this thread: http://www.excelforum.com/showthread...hreadid=545300 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting days in month
Hi James
Assuming you have the Analysis Toolpak loaded (ToolsAddinsAnalysis Toolpak), then you can use the Eomonth() function. With Start date in A1, End Date in B1 days in May =EOMONTH(A1,0)-A1+1 days in June =B1-EOMONTH(A1,0) Format the cells containing the formula as General. Both formulae assume that you are counting the whole of the start date and the whole of the end date -- Regards Roger Govier "jamesjohn" wrote in message ... How can I separate days into different months and count them ? If I have a date of arrival and a date of departu 17/05/2006 to 22/06/2006 how can I count the numberof days in May and the number of days in June and have them separated ? Thanks in advance. -- jamesjohn ------------------------------------------------------------------------ jamesjohn's Profile: http://www.excelforum.com/member.php...fo&userid=9692 View this thread: http://www.excelforum.com/showthread...hreadid=545300 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting days in month
Hi!
Try this A2 = 17/05/2006 B2 = 22/06/2006 D1 = header = Month E1 = header = Days Enter this formula in D2: =IF(DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1)<B$2,TEXT(DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1),"mmmm"),"") Enter this formula in E2: =IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1))<B$2,MIN(DATE(YEAR(A$2),MONTH(A$2)+ROW()-1,0),B$2)-MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1))+1,"") Select both D2 and E2 and copy down until you get blanks. Biff "jamesjohn" wrote in message ... How can I separate days into different months and count them ? If I have a date of arrival and a date of departu 17/05/2006 to 22/06/2006 how can I count the numberof days in May and the number of days in June and have them separated ? Thanks in advance. -- jamesjohn ------------------------------------------------------------------------ jamesjohn's Profile: http://www.excelforum.com/member.php...fo&userid=9692 View this thread: http://www.excelforum.com/showthread...hreadid=545300 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting days in month
Thanks very much ! It all seems so easy afterwards. Now I have another one: If I have different tarifs for different dates, how can I count how many days are in each tarif for different start and end dates ? Arrive 08/07 and Leave 13/08...how many days at tarif 5 and how many days at tarif 6? Start End Tarif 01/03/2006 13/04/2006 1 13/04/2006 17/04/2006 2 18/04/2006 16/06/2006 3 17/06/2006 07/07/2006 4 08/07/2006 04/08/2006 5 05/08/2006 25/08/2006 6 26/08/2006 08/09/2006 7 09/09/2006 28/10/2006 8 Driving me MAD ! -- jamesjohn ------------------------------------------------------------------------ jamesjohn's Profile: http://www.excelforum.com/member.php...fo&userid=9692 View this thread: http://www.excelforum.com/showthread...hreadid=545300 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting days in month
Hi James
Assuming you have the Analysis Toolpak loaded (ToolsAddinsAnalysis Toolpak), then you can use the Eomonth() function. With Start date in A1, End Date in B1 days in tarrif5 =EOMONTH(A1,0)-A1+1 days in tarrif6 =B1-EOMONTH(A1,0) Format the cells containing the formula as General. Both formulae assume that you are counting the whole of the start date and the whole of the end date -- Regards Roger Govier "jamesjohn" wrote in message ... Thanks very much ! It all seems so easy afterwards. Now I have another one: If I have different tarifs for different dates, how can I count how many days are in each tarif for different start and end dates ? Arrive 08/07 and Leave 13/08...how many days at tarif 5 and how many days at tarif 6? Start End Tarif 01/03/2006 13/04/2006 1 13/04/2006 17/04/2006 2 18/04/2006 16/06/2006 3 17/06/2006 07/07/2006 4 08/07/2006 04/08/2006 5 05/08/2006 25/08/2006 6 26/08/2006 08/09/2006 7 09/09/2006 28/10/2006 8 Driving me MAD ! -- jamesjohn ------------------------------------------------------------------------ jamesjohn's Profile: http://www.excelforum.com/member.php...fo&userid=9692 View this thread: http://www.excelforum.com/showthread...hreadid=545300 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting days in month
Roger, I haven't made myself clear, because you have sent me the same formula as for finding how many days in each month. The table in my post is the start and end dates of the tarifs. You will see that some tarifs only last a couple of weeks and are in the same month and other tarifs take in dates from 3 different months. I need to calculate how many days of each month are in each different tarif. Hope this makes sense ! -- jamesjohn ------------------------------------------------------------------------ jamesjohn's Profile: http://www.excelforum.com/member.php...fo&userid=9692 View this thread: http://www.excelforum.com/showthread...hreadid=545300 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting days in month
Hi James
I set up the end dates of your tariff periods and the period number in cells A2:B9 and named this Periods. Your full range of dates with start and end dates for each period, I copied to cells D2:E9 having adjusted the value in cell D3 to be 14/04/06 not 13/04/06 In C2 I entered the following formula =E2-D2+1 and copied down through C3:C9 I then named range B2:E9 as Tariffs I used F2 to Enter Start Date and G2 to enter End Date In F1 I entered the formula =VLOOKUP(F2,Periods,2)+1 and copied across to G1 In Cell H2 I entered the following formula =IF(AND($F1<B2,$G1B2),VLOOKUP(B2,tariff,2,0), IF($F1=B2,VLOOKUP($F1,tariff,2,0)-($F2-VLOOKUP($F1,tariff,3,0)), IF($G1=B2,VLOOKUP($G1,tariff,2,0)-(VLOOKUP($G1,tariff,4,0)-$G2)+1,0))) and copied down through cells H3:H9 Then inputting start and end dates to F2 and G2 gives the number of days falling into each tariff period. -- Regards Roger Govier "jamesjohn" wrote in message ... Roger, I haven't made myself clear, because you have sent me the same formula as for finding how many days in each month. The table in my post is the start and end dates of the tarifs. You will see that some tarifs only last a couple of weeks and are in the same month and other tarifs take in dates from 3 different months. I need to calculate how many days of each month are in each different tarif. Hope this makes sense ! -- jamesjohn ------------------------------------------------------------------------ jamesjohn's Profile: http://www.excelforum.com/member.php...fo&userid=9692 View this thread: http://www.excelforum.com/showthread...hreadid=545300 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Desperately trying to build a paid time off accrual worksheet... | Excel Worksheet Functions | |||
challenging formula(for me), counting days between dates for multipleyears | Excel Worksheet Functions | |||
Calculate Days in a Month | Excel Discussion (Misc queries) | |||
Counting Entries by Month and Year | Excel Worksheet Functions | |||
Counting days and Configurations together | Excel Worksheet Functions |