Calculation of month days

Closed
dany - Jul 27, 2009 at 04:12 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 24, 2010 at 06:56 PM
Hello,
i want to change the total no. of days every month,like juky caries 31 and august cary 30 and feb cary 28 ,
so according to the month I want my the total days sould be calculated automaticly.
any formula?

8 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 27, 2009 at 05:57 AM
do this simple experiment
in A1 type
1/1/09
highlight A1 to A12
in the series window
"series in" click "columns" if not elaeady done
click "date" under "type"
click "month" under "date unit"
step is 1

click ok

your will get A1 to A12 like this

in B1 copy paste this formula
=(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
copy B1 down
format this column B as date
in C1 copy paste ths formula

=(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
copy C1 down
format column c as number with no decimal places.

you will get

1/1/2009 31-Jan-09 31
2/1/2009 28-Feb-09 28
3/1/2009 31-Mar-09 31
4/1/2009 30-Apr-09 30
5/1/2009 31-May-09 31
6/1/2009 30-Jun-09 30
7/1/2009 31-Jul-09 31
8/1/2009 31-Aug-09 31
9/1/2009 30-Sep-09 30
10/1/2009 31-Oct-09 31
11/1/2009 30-Nov-09 30
12/1/2009 31-Dec-09 31

is this what you want
hi venkat thanx for your help,
let me explain wat exactly I need,
i have a attandance sheet and I inserted every formula into that attn sheet.
the sheet contains our billing and the workers salary.everything I got the main problen in the attn sheetis
our co.account people calculate the bill and salary as per the days of month.
only thing I need to know is if I type the month name in the work sheet can I get the no. of days.
suppose if iam typing june can I get the no. of days.
Hi danny,

I'm not as good as Venkat but this is what you could do.

Make an extra sheet with the month under each other and the number of days next to them (you could hide this sheet).

In the cell you want the number of days use a V.LookUp function. The search value will be the cell you enter the name of the month in. The matrix to search in will be on the hidden sheet and the columnindexnumber will be 2.

Hope this is useful.

Best regards,
Trowa
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 27, 2009 at 08:44 PM
Dannny

Trowa has given you the correct solution. You can use my sheet as vlookup referecne
if there is problem post back.

Didn't find the answer you are looking for?

Thanx trowa,

i got ur reply but please can u send the work step by step how to st the formula
Hi Danny,

Happy to see you are happy with my solution.

On sheet1 you put the name of the month in cell A1 then you the number of days in B1.
On sheet2 (the sheet you can hide) you input the names of each month in A1:A12.
Put the days of each month in B1:B12.

Now input the following formula in cell B1 from sheet1:
=v.lookup(A1;sheet2!A1:B12;2)

If you don't get the right result you might want to sort the data on sheet2:
select all month and days. Data>Sort>Sort column A>OK.

If you get an error you might want to change the ";" into a "," in the formula depending on your excel version.

Hope this info is sufficient for you.

Best regards,
Trowa
9th of august to the 23rd of june? how long would that be?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 24, 2010 at 06:56 PM
lea: if you are asking about number of days then do a simple difference between the two dates