Problems with showing the correct number of days in a month?! HELP!!!!

B

bftznk

New Member
Joined
Apr 17, 2013
Messages
16
Sometimes there are 28/29/30/31 days in a month in a year, right?! What's the simplest ways to meet the following task?

1. The Year or/and month can be change
2. Once the date is change the Week of days in different(Mon, Tue...) would also be changed correctly
3. Show correctly if there are 28/29/30/31,


57935302.jpg

:confused::confused:
Do I need to use VB,Marco or
someting else?! Plz Help!
 
Sort by date Sort by votes
Hello,

My formulas are working quite well.

It shows the wrong days of the week because you're now using a different table from what you originally specified. You really needed to show us the actual table format you are using for us to be of any help. Your dates are showing vertically now instead of horizontally for a start!

A2 = enter year
B2 = enter month number
C2 =DATE(A2,B2,1), format to "d"
D2 =C2, copy down to D32
E2 =C2, format to "ddd", copy down to E32

A3 =IF(ISERROR(YEAR(C3)),"",YEAR(C3)), copy down to A32
B3 =IF(ISERROR(MONTH(C3)),"",MONTH(C3)), copy down to B32
C3 =IF(ISERROR(IF(C2+1 > EOMONTH(C2,0),"",C2+1)),"",IF(C2+1 > EOMONTH(C2,0),"",C2+1)), copy down to C32.

Result:

[TABLE="width: 260"]
<COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=4 width=71><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl25, width: 71, bgcolor: transparent, align: center"]Year[/TD]
[TD="class: xl25, width: 71, bgcolor: transparent, align: center"]Month[/TD]
[TD="class: xl25, width: 71, bgcolor: transparent, align: center"]Date[/TD]
[TD="class: xl25, width: 71, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: center"]Day[/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: silver, align: center"]2013[/TD]
[TD="class: xl26, bgcolor: silver, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]01/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Tue[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]2[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]02/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Wed[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]3[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]03/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Thu[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]4[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]04/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Fri[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]05/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Sat[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]6[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]06/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Sun[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]7[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]07/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Mon[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]8[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]08/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Tue[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]9[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]09/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Wed[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]10[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]10/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Thu[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]11[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]11/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Fri[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]12[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]12/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Sat[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]13[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]13/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Sun[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]14[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]14/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Mon[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]15[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]15/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Tue[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]16[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]16/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Wed[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]17[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]17/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Thu[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]18[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]18/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Fri[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]19[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]19/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Sat[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]20[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]20/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Sun[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]21[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]21/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Mon[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]22[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]22/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Tue[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]23[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]23/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Wed[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]24[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]24/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Thu[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]25[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]25/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Fri[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]26[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]26/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Sat[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]27[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]27/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Sun[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]28[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]28/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Mon[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]29[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]29/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Tue[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]30[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]30/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Wed[/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: transparent, align: center"]2013[/TD]
[TD="class: xl25, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl27, bgcolor: transparent, align: center"]31[/TD]
[TD="class: xl28, bgcolor: transparent, align: center"]31/01/2013[/TD]
[TD="class: xl24, bgcolor: transparent, align: center"]Thu[/TD]
[/TR]
</TBODY>[/TABLE]

Let me know how it works out.
 
Upvote 0
wow, it works vertically, let see if it work horizontally ;) Thx,you are genius, Any books or tranning you can recommend?!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Pops message box when run userform based on days for current month
Replies
0
Views
105
Ali M
A
K
  • Question Question
How to Report on Time by Month/Year when time data only shows week start date and time by Days in the week
Replies
1
Views
88
JamesCanale
JamesCanale
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
180
Ali M
A
A
  • Solved
copy data from listbox on form to sheet based on restrictions
2
Replies
14
Views
282
Ali M
A
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
126
Sufiyan97
Sufiyan97

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top