Working days question

[Someone]

Hello

I use the dhAddWorkDaysA function (see here
http://www.mvps.org/access/datetime/date0012.htm) and it works great for
most of my needs in calculating the due date/time given the timeframe
specified (I just thought it'd be easier to help the users).

However, there is one thing that I need to do which I'm not sure whether
this function covers. If it does, I don't know what criteria to specify
and, if it doesn't, I don't know what to do to get the results I need; so
I'm hoping someone could help me.

This is how I'm currently calling the function:

For 1 hour onwards: Me![DueDate] = DateAdd("h", 1, LogDate)
For 1 working day onward: Me![DueDate] = dhAddWorkDaysA(1, LogDate)
For 2 working days onwards: Me![DueDate] = dhAddWorkDaysA(2, LogDate)

I also need to have a timeframe which equates to the end of the following
business day (5 pm). For clarity, let's say at 10.14 am tomorrow (Tuesday)
a user creates a new record using this timeframe, the due date will be by 5
pm on the next day (Wednesday).

Could somebody point me in the right direction please?

Many thanks
M

 

[Jeff Boyce]

Are you saying you need to go to 5pm on the day after the day on which the
date is recorded?

If so, have you looked at the DateAdd() (or DateSerial) function as a way to
get the next day, and added the fractional portion of a day represented by
5pm (i.e., 17/24)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Someone]

Hello Jeff

Thanks very much for taking the time to help me.

This is what I've managed to do in the meantime to get what I wanted.

As I need to ensure the next day is a working day, I thought I could still
use the dhAddWorkDaysA function by adding 1 day. In addition to this, as
the due by time will always be 17:00 hours, I thought I could just specify
the time manually, then concatenate the two and plonk the value in as a
string, thus:

strDate = dhAddWorkDaysA(1, LogDate)
strDate = Format(strDate, "dd/mm/yyyy")
strTime = "17:00:00"
strDue = strDate & " " & strTime
Me![DueDate] = strDue

Is that acceptable or just plain awful?

It works, anyway!

Thanks
M

Are you saying you need to go to 5pm on the day after the day on which the
date is recorded?

If so, have you looked at the DateAdd() (or DateSerial) function as a way
to get the next day, and added the fractional portion of a day represented
by 5pm (i.e., 17/24)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello

I use the dhAddWorkDaysA function (see here
http://www.mvps.org/access/datetime/date0012.htm) and it works great for
most of my needs in calculating the due date/time given the timeframe
specified (I just thought it'd be easier to help the users).

However, there is one thing that I need to do which I'm not sure whether
this function covers. If it does, I don't know what criteria to specify
and, if it doesn't, I don't know what to do to get the results I need; so
I'm hoping someone could help me.

This is how I'm currently calling the function:

For 1 hour onwards: Me![DueDate] = DateAdd("h", 1, LogDate)
For 1 working day onward: Me![DueDate] = dhAddWorkDaysA(1, LogDate)
For 2 working days onwards: Me![DueDate] = dhAddWorkDaysA(2, LogDate)

I also need to have a timeframe which equates to the end of the following
business day (5 pm). For clarity, let's say at 10.14 am tomorrow
(Tuesday) a user creates a new record using this timeframe, the due date
will be by 5 pm on the next day (Wednesday).

Could somebody point me in the right direction please?

Many thanks
M

 

[Douglas J. Steele]

While it's not plain awful, it's certainly not the best approach. <g>

What you need to realize is that under the covers, Dates are simply numbers
(specifically, 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day)

That means that strDate = Format(strDate, "dd/mm/yyyy") really has no
meaning: format simply affects how the value is presented, not how it's
stored.

All you need is:

Me![DueDate] = dhAddWorkDaysA(1, LogDate) + #17:00:00#

or


Me![DueDate] = dhAddWorkDaysA(1, LogDate) + TimeSerial(17, 0, 0)

You're doing what's referred to as "coercing" (changing a value from one
represenation to another) that's unnecessary, and can possibly introduce
errors into the values.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Jeff

Thanks very much for taking the time to help me.

This is what I've managed to do in the meantime to get what I wanted.

As I need to ensure the next day is a working day, I thought I could still
use the dhAddWorkDaysA function by adding 1 day. In addition to this, as
the due by time will always be 17:00 hours, I thought I could just specify
the time manually, then concatenate the two and plonk the value in as a
string, thus:

strDate = dhAddWorkDaysA(1, LogDate)
strDate = Format(strDate, "dd/mm/yyyy")
strTime = "17:00:00"
strDue = strDate & " " & strTime
Me![DueDate] = strDue

Is that acceptable or just plain awful?

It works, anyway!

Thanks
M

Are you saying you need to go to 5pm on the day after the day on which
the date is recorded?

If so, have you looked at the DateAdd() (or DateSerial) function as a way
to get the next day, and added the fractional portion of a day
represented by 5pm (i.e., 17/24)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello

I use the dhAddWorkDaysA function (see here
http://www.mvps.org/access/datetime/date0012.htm) and it works great for
most of my needs in calculating the due date/time given the timeframe
specified (I just thought it'd be easier to help the users).

However, there is one thing that I need to do which I'm not sure whether
this function covers. If it does, I don't know what criteria to specify
and, if it doesn't, I don't know what to do to get the results I need;
so I'm hoping someone could help me.

This is how I'm currently calling the function:

For 1 hour onwards: Me![DueDate] = DateAdd("h", 1, LogDate)
For 1 working day onward: Me![DueDate] = dhAddWorkDaysA(1, LogDate)
For 2 working days onwards: Me![DueDate] = dhAddWorkDaysA(2, LogDate)

I also need to have a timeframe which equates to the end of the
following business day (5 pm). For clarity, let's say at 10.14 am
tomorrow (Tuesday) a user creates a new record using this timeframe,
the due date will be by 5 pm on the next day (Wednesday).

Could somebody point me in the right direction please?

Many thanks
M

 

[Someone]

Interesting. It seems so straightforward!

That said, I tried incorporating both ideas, but neither worked as expected.
Each of them returned a different date and time.

I'll have a look into this a bit more.

Thanks
M

While it's not plain awful, it's certainly not the best approach. <g>

What you need to realize is that under the covers, Dates are simply
numbers (specifically, 8 byte floating point numbers, where the integer
portion represents the date as the number of days relative to 30 Dec,
1899, and the decimal portion represents the time as a fraction of a day)

That means that strDate = Format(strDate, "dd/mm/yyyy") really has no
meaning: format simply affects how the value is presented, not how it's
stored.

All you need is:

Me![DueDate] = dhAddWorkDaysA(1, LogDate) + #17:00:00#

or


Me![DueDate] = dhAddWorkDaysA(1, LogDate) + TimeSerial(17, 0, 0)

You're doing what's referred to as "coercing" (changing a value from one
represenation to another) that's unnecessary, and can possibly introduce
errors into the values.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Jeff

Thanks very much for taking the time to help me.

This is what I've managed to do in the meantime to get what I wanted.

As I need to ensure the next day is a working day, I thought I could
still use the dhAddWorkDaysA function by adding 1 day. In addition to
this, as the due by time will always be 17:00 hours, I thought I could
just specify the time manually, then concatenate the two and plonk the
value in as a string, thus:

strDate = dhAddWorkDaysA(1, LogDate)
strDate = Format(strDate, "dd/mm/yyyy")
strTime = "17:00:00"
strDue = strDate & " " & strTime
Me![DueDate] = strDue

Is that acceptable or just plain awful?

It works, anyway!

Thanks
M

Are you saying you need to go to 5pm on the day after the day on which
the date is recorded?

If so, have you looked at the DateAdd() (or DateSerial) function as a
way to get the next day, and added the fractional portion of a day
represented by 5pm (i.e., 17/24)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello

I use the dhAddWorkDaysA function (see here
http://www.mvps.org/access/datetime/date0012.htm) and it works great
for most of my needs in calculating the due date/time given the
timeframe specified (I just thought it'd be easier to help the users).

However, there is one thing that I need to do which I'm not sure
whether this function covers. If it does, I don't know what criteria
to specify and, if it doesn't, I don't know what to do to get the
results I need; so I'm hoping someone could help me.

This is how I'm currently calling the function:

For 1 hour onwards: Me![DueDate] = DateAdd("h", 1, LogDate)
For 1 working day onward: Me![DueDate] = dhAddWorkDaysA(1, LogDate)
For 2 working days onwards: Me![DueDate] = dhAddWorkDaysA(2, LogDate)

I also need to have a timeframe which equates to the end of the
following business day (5 pm). For clarity, let's say at 10.14 am
tomorrow (Tuesday) a user creates a new record using this timeframe,
the due date will be by 5 pm on the next day (Wednesday).

Could somebody point me in the right direction please?

Many thanks
M