Count number of work days for each month between a range of Dates (1 Viewer)

[raskew]

OK, I take it back. 3072 is actually 5/29/1908. When it's formatted as "Medium Date", it returns 29-May-08, which at first glance leads one to believe we're talking 2008, rather than 1908.

Still think there's some very clever usage involved in the code. The code involves numbers 000 to 999 which I can now see equates to 12/30/1899 to 9/25/1902. It's then modified so as to represent the inputted dates.

Bob

 

[Jon K]

Bob,

When you look at the expression for the Dates field in the WorkingDays query, you will see that Khawar ingeniously added the Numbers (that is, 0 to 999) to the From date on the form:

Dates: [Numbers] + CDate(forms!form1!fdate)

It has nothing to do with the format of the Numbers 0 to 999.


Given the OP's sample data, adding 999 days to the From date is more than enough to cover the input date range. Should the input date range exceed 1,000 days, the Numbers will have to be increased.

Jon
.

 

[raskew]

Jon -

Thanks for your insight. However, open the Numbers query in design view, change the format from Medium Date to Long Date and take a look at the returns: 12/30/1899 to 9/25/1902. Added: It is, in fact, converting 0 to 999 to dates (albeit strings, which require the cdate() function to return true dates), which are then used in the balance of the application. It's not, so far as I can see, adding 0 to 999 to the starting date, e.g. dateadd("d", [Number], StartDate). What am I missing?

Still confused but 'getting there'.

Best wishes - Bob

 

[Jon K]

Bob,

See the attached database, in which I have removed the Short Date format from the Numbers query so that it returns only 0 to 999. Khawar's code still works.

I have also added a MyWorkingDays query which is modified from Khawar's WorkingDays query. When MyWorkingDays is run from the Form, it will return 1,000 records starting from the inputted From date. This confirms that Khawar's query adds the numbers 0 to 999 to the inputted From date on the Form.

Hope it helps.

Jon
.

 

[raskew]

Jon -

Thanks so much. As I thought about this (before you posted your most recent post) started to realize that the date business was in fact extraneous.

Thanks again for your time and patience.

Best Wishes - Bob

 

[EMP]

Yes, it's extraneous. It's also confusing to the human mind.

I can easily see what's happening when I see some expressions like
Date2 - Date1. It will return the number of days between two dates.

But I had a hard time making out what was really happening in an expression
Date1 + Date2.

^

 

[raskew]

For sure! I let myself get seriously 'wrapped around the axle' by the numbers displayed as dates (not sure why the originator did that). Nonetheless, there was some excellent coding involved in Khawar's solution.

Best wishes - Bob

 

[khawar]

Hi Raskew sorry I was too much busy for two days and also sorry for making you so confused

there is nothing hidden in this example

dont consider the dates too much confusing

first of all have a look at query numbers

these are just numbers and have nothing to do with their format i-e medium date so consider them only as number 0 to 999

now come to the query working days

go to first column date it shows num+cdate(forms!form1!fdate)

fdate is the date from where the calculation is to be started

adding fdate to first number in the query numbers will give you the same date which is from date(i-e starting Date) on the form1

coz you are adding 0 to form's start date second will forms's start date+1
and so on

then i limitize the range upto last date(tDate) for which work days are required

now if any thing is not clear dont hesitate to ask me


regards


Khawar

 

[raskew]

Thanks, I do understand it now. As previously stated, I was (erroneously) reading some special significance into the fact that the numbers were being displayed in medium date format and that's where my confusion came in.

Best wishes - Bob

 

[Wrinkles]

Many thanks again Khawan and for the amended sample with the workdays. I must admit I have been busy on another topic/project and will look through your code in more detail when I return.

Thanks again for everyone who has contributed - much appreciated.