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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 24, 2010 at 06:56 PM
Related:
- Calculation of month days
- Skype last seen days ago - Guide
- Calculation is incomplete. recalculate before saving - Guide
- Vat calculation formula - Guide
- How to calculate 90 days from date in excel - Guide
- Time of day clock stopped - Guide
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
Jul 27, 2009 at 05:57 AM
do this simple experiment
in A1 type
1/1/09
highlight A1 to A12
click edit(menut)-fill-series
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
in A1 type
1/1/09
highlight A1 to A12
click edit(menut)-fill-series
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.
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
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
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.
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?
Ask a question
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 24, 2010 at 06:56 PM
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
