• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

hello chandoo.org

K

keen

New Member
Hello All,

I am fairly new at excel but your site has allowed me to solve any issues I have come across so far. Anyway I wanted to give something back, so here is a little sheet I put together to automatically calculate the days of any given month, and some extra stuff.
I put this together, basically because I'm lazy, and I didn't want to manually change all the dates in a report I run every month. :D

Let me know if you have any questions or would like me to break down some of the formula's I've used.

Thanks Chandoo.org
 

Attachments

  • Dates.xlsx
    11.2 KB · Views: 7
Keen

Firstly, Welcome to the Chandoo.org Forums

A few comments:

In Cell E6 you have a formula for the 1st of the month =DATE(YEAR(E4),MONTH(E4),1)
Another formula for the first of the month is =EOMONTH(E4,-1)+1
Excel functions are highly optimised and it is likely that a single function will be much faster than a formula with 3 functions but I haven't ran it through a speed test to check

In cell E7 you have Count of Workdays =COUNT(workdays)
I think that should be =COUNT(workingdays)
The named formula Workdays doesn't exist
 
Hi Hui,
thanks, and thanks again. the "workingdays" range thing happened when I made a version to upload here and the file lost the ranges I had made before. I only put the ranges back in this file for illustration, because none of the formula's actually use named ranges. (Except of course for the one you found but to calculate the number of weekdays I found =NETWORKDAYS(E6,E9) is more elegant.)
The things I struggled most with in this sheet are for instance finding the first weekday of the month compared to first monday of the month:
=IF(WEEKDAY($E$6)=1,$E$6+1,IF(WEEKDAY($E$6)=7,$E$6+2,$E$6))
=IF((WEEKDAY($E$6)=2),$E$6,IF(WEEKDAY($E$6)=1,E6+1,($E$6-$E$5+9)))
I couldn't figure out if there are optimised functions for that..
 
Keen

I think you may have posted in the wrong forum and by doing so have restricted the number of members who would have been able to view and probably help with your post or do as you have suggested and use the workbook or like Hui help with the formulas. The Lounge is where we can talk and discuss non excel subjects and have a moan about Excel...the top post in the lounge..."Hello..Introduce yourself" is there to do as it says...Your title for a post says nothing about your post content and again will limit those that would have had a look.
 
Hi bobhc,
very good point.. I will consider reposting tomorrow with appropriate adjustments.
Thanks.
 
You must log in or register to reply here.
Back
Top