calculate dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I am having trouble calculating the number of days an account is
outstanding (still in process, has not been completed). I consider an
account outstanding if it has been open for more than 10days since the first
open date. I can calculate the number of dates but i can not get it to not
calculate weekend too. Here is what I have so far, something fairly simply:

DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED]
"Scla" is the table name and "date to manager and received" is the date of
when the account has been open.

I just want to put in a formula that is simple and if possible no coding
because I am not familiar with that.

Is it possible to build an expression like this
DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED] / 7 * 2 -
Now()-[SCLA]![DATE TO MANGER AND RECEIVED]

Like take the anwser divide by 7 and multiply by 2 and subtract it by the
total number of days. Simple calculation of adding and subtracting. Is it
possible to do that?

I am sorry I am not clear in explaining my situation and formula. Please
Help in any way.! Thanks in advance! Have a great day!
 
The DateDiff function (see Access VBA Help -abbreviated below) will do
exactly what you want.

Your example: DateDiff(["d",[DATE TO MANGER AND RECEIVED] - Date() )

Not sure if you're trying to show the number of biweekly periods, if so, the
above would be: DateDiff(["ww",[DATE TO MANGER AND RECEIVED] - Date() )

-Ed


==========================================
DateDiff Function
Returns a Variant (Long) specifying the number of time intervals between two
specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you
use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in
the calculation.
firstdayofweek Optional. A constant that specifies the first day of
the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of
the year. If not specified, the first week is assumed to be the week in
which January 1 occurs.



Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second


Remarks

You can use the DateDiff function to determine how many specified time
intervals exist between two dates. For example, you might use DateDiff to
calculate the number of days between two dates, or the number of weeks
between today and the end of the year.

To calculate the number of days between date1 and date2, you can use either
Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff
returns the number of weeks between the two dates. If date1 falls on a
Monday, DateDiff counts the number of Mondays until date2. It counts date2
but not date1. If interval is Week ("ww"), however, the DateDiff function
returns the number of calendar weeks between the two dates. It counts the
number of Sundays between date1 and date2. DateDiff counts date2 if it falls
on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

If date1 refers to a later point in time than date2, the DateDiff function
returns a negative number.

========================================

tina said:
Hello,
I am having trouble calculating the number of days an account is
outstanding (still in process, has not been completed). I consider an
account outstanding if it has been open for more than 10days since the
first
open date. I can calculate the number of dates but i can not get it to
not
calculate weekend too. Here is what I have so far, something fairly
simply:

DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED]
"Scla" is the table name and "date to manager and received" is the date of
when the account has been open.

I just want to put in a formula that is simple and if possible no coding
because I am not familiar with that.

Is it possible to build an expression like this
DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED] / 7 * 2 -
Now()-[SCLA]![DATE TO MANGER AND RECEIVED]

Like take the anwser divide by 7 and multiply by 2 and subtract it by the
total number of days. Simple calculation of adding and subtracting. Is
it
possible to do that?

I am sorry I am not clear in explaining my situation and formula. Please
Help in any way.! Thanks in advance! Have a great day!
Click to expand...
 
The DateDiff function (see Access VBA Help -abbreviated below) will do
exactly what you want.

Your example: DateDiff(["d",[DATE TO MANGER AND RECEIVED] - Date() )
Click to expand...

Small but critical correction, Ed: replace the - with a comma.


John W. Vinson[MVP]
 
Hello thanks for your replies! I've finally got datediff to work, I tired it
long time before and it did not work the way i wanted so i decided to just do
a manual caulculation in access, which returned with never ending decimals
after the number. Right now i got it to show just the number of days! thanks!

But how Do i get it to calculate the number of days without the weekends?
How do I specify it to calculate monday to friday only? right now I have:

DAYS OUTSTANDING: DateDiff("d",[Date to Manger and received],Date())

This includes weekend and weekdays. I just want the number of days on the
weekdays. Again, Thanks for your help and detail instructions.

Ed Robichaud said:
The DateDiff function (see Access VBA Help -abbreviated below) will do
exactly what you want.

Your example: DateDiff(["d",[DATE TO MANGER AND RECEIVED] - Date() )

Not sure if you're trying to show the number of biweekly periods, if so, the
above would be: DateDiff(["ww",[DATE TO MANGER AND RECEIVED] - Date() )

-Ed


==========================================
DateDiff Function
Returns a Variant (Long) specifying the number of time intervals between two
specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you
use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in
the calculation.
firstdayofweek Optional. A constant that specifies the first day of
the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of
the year. If not specified, the first week is assumed to be the week in
which January 1 occurs.



Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second


Remarks

You can use the DateDiff function to determine how many specified time
intervals exist between two dates. For example, you might use DateDiff to
calculate the number of days between two dates, or the number of weeks
between today and the end of the year.

To calculate the number of days between date1 and date2, you can use either
Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff
returns the number of weeks between the two dates. If date1 falls on a
Monday, DateDiff counts the number of Mondays until date2. It counts date2
but not date1. If interval is Week ("ww"), however, the DateDiff function
returns the number of calendar weeks between the two dates. It counts the
number of Sundays between date1 and date2. DateDiff counts date2 if it falls
on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

If date1 refers to a later point in time than date2, the DateDiff function
returns a negative number.

========================================

tina said:
Hello,
I am having trouble calculating the number of days an account is
outstanding (still in process, has not been completed). I consider an
account outstanding if it has been open for more than 10days since the
first
open date. I can calculate the number of dates but i can not get it to
not
calculate weekend too. Here is what I have so far, something fairly
simply:

DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED]
"Scla" is the table name and "date to manager and received" is the date of
when the account has been open.

I just want to put in a formula that is simple and if possible no coding
because I am not familiar with that.

Is it possible to build an expression like this
DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED] / 7 * 2 -
Now()-[SCLA]![DATE TO MANGER AND RECEIVED]

Like take the anwser divide by 7 and multiply by 2 and subtract it by the
total number of days. Simple calculation of adding and subtracting. Is
it
possible to do that?

I am sorry I am not clear in explaining my situation and formula. Please
Help in any way.! Thanks in advance! Have a great day!
Click to expand...
Click to expand...
 
Tina

Consider setting a Reference to the Excel object library and using the
NETWORKDAYS() function from Excel.

Good luck!

Jeff Boyce
<Access MVP>

tina said:
Hello thanks for your replies! I've finally got datediff to work, I tired
it
long time before and it did not work the way i wanted so i decided to just
do
a manual caulculation in access, which returned with never ending decimals
after the number. Right now i got it to show just the number of days!
thanks!

But how Do i get it to calculate the number of days without the weekends?
How do I specify it to calculate monday to friday only? right now I have:

DAYS OUTSTANDING: DateDiff("d",[Date to Manger and received],Date())

This includes weekend and weekdays. I just want the number of days on the
weekdays. Again, Thanks for your help and detail instructions.

Ed Robichaud said:
The DateDiff function (see Access VBA Help -abbreviated below) will do
exactly what you want.

Your example: DateDiff(["d",[DATE TO MANGER AND RECEIVED] - Date() )

Not sure if you're trying to show the number of biweekly periods, if so,
the
above would be: DateDiff(["ww",[DATE TO MANGER AND RECEIVED] - Date() )

-Ed


==========================================
DateDiff Function
Returns a Variant (Long) specifying the number of time intervals between
two
specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time
you
use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in
the calculation.
firstdayofweek Optional. A constant that specifies the first day of
the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week
of
the year. If not specified, the first week is assumed to be the week in
which January 1 occurs.



Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second


Remarks

You can use the DateDiff function to determine how many specified time
intervals exist between two dates. For example, you might use DateDiff to
calculate the number of days between two dates, or the number of weeks
between today and the end of the year.

To calculate the number of days between date1 and date2, you can use
either
Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff
returns the number of weeks between the two dates. If date1 falls on a
Monday, DateDiff counts the number of Mondays until date2. It counts
date2
but not date1. If interval is Week ("ww"), however, the DateDiff function
returns the number of calendar weeks between the two dates. It counts the
number of Sundays between date1 and date2. DateDiff counts date2 if it
falls
on a Sunday; but it doesn't count date1, even if it does fall on a
Sunday.

If date1 refers to a later point in time than date2, the DateDiff
function
returns a negative number.

========================================

tina said:
Hello,
I am having trouble calculating the number of days an account is
outstanding (still in process, has not been completed). I consider an
account outstanding if it has been open for more than 10days since the
first
open date. I can calculate the number of dates but i can not get it to
not
calculate weekend too. Here is what I have so far, something fairly
simply:

DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED]
"Scla" is the table name and "date to manager and received" is the date
of
when the account has been open.

I just want to put in a formula that is simple and if possible no
coding
because I am not familiar with that.

Is it possible to build an expression like this
DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED] / 7 * 2 -
Now()-[SCLA]![DATE TO MANGER AND RECEIVED]

Like take the anwser divide by 7 and multiply by 2 and subtract it by
the
total number of days. Simple calculation of adding and subtracting.
Is
it
possible to do that?

I am sorry I am not clear in explaining my situation and formula.
Please
Help in any way.! Thanks in advance! Have a great day!
Click to expand...
Click to expand...
Click to expand...
 
Why not either http://www.mvps.org/access/datetime/date0006.htm or
http://www.mvps.org/access/datetime/date0012.htm at "The Access Web", Jeff?

Jeez: Excel indeed! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jeff Boyce said:
Tina

Consider setting a Reference to the Excel object library and using the
NETWORKDAYS() function from Excel.

Good luck!

Jeff Boyce
<Access MVP>

tina said:
Hello thanks for your replies! I've finally got datediff to work, I
tired it
long time before and it did not work the way i wanted so i decided to
just do
a manual caulculation in access, which returned with never ending
decimals
after the number. Right now i got it to show just the number of days!
thanks!

But how Do i get it to calculate the number of days without the weekends?
How do I specify it to calculate monday to friday only? right now I have:

DAYS OUTSTANDING: DateDiff("d",[Date to Manger and received],Date())

This includes weekend and weekdays. I just want the number of days on
the
weekdays. Again, Thanks for your help and detail instructions.

Ed Robichaud said:
The DateDiff function (see Access VBA Help -abbreviated below) will do
exactly what you want.

Your example: DateDiff(["d",[DATE TO MANGER AND RECEIVED] - Date() )

Not sure if you're trying to show the number of biweekly periods, if so,
the
above would be: DateDiff(["ww",[DATE TO MANGER AND RECEIVED] - Date() )

-Ed


==========================================
DateDiff Function
Returns a Variant (Long) specifying the number of time intervals between
two
specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time
you
use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use
in
the calculation.
firstdayofweek Optional. A constant that specifies the first day
of
the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week
of
the year. If not specified, the first week is assumed to be the week in
which January 1 occurs.



Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second


Remarks

You can use the DateDiff function to determine how many specified time
intervals exist between two dates. For example, you might use DateDiff
to
calculate the number of days between two dates, or the number of weeks
between today and the end of the year.

To calculate the number of days between date1 and date2, you can use
either
Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff
returns the number of weeks between the two dates. If date1 falls on a
Monday, DateDiff counts the number of Mondays until date2. It counts
date2
but not date1. If interval is Week ("ww"), however, the DateDiff
function
returns the number of calendar weeks between the two dates. It counts
the
number of Sundays between date1 and date2. DateDiff counts date2 if it
falls
on a Sunday; but it doesn't count date1, even if it does fall on a
Sunday.

If date1 refers to a later point in time than date2, the DateDiff
function
returns a negative number.

========================================

Hello,
I am having trouble calculating the number of days an account is
outstanding (still in process, has not been completed). I consider an
account outstanding if it has been open for more than 10days since the
first
open date. I can calculate the number of dates but i can not get it
to
not
calculate weekend too. Here is what I have so far, something fairly
simply:

DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED]
"Scla" is the table name and "date to manager and received" is the
date of
when the account has been open.

I just want to put in a formula that is simple and if possible no
coding
because I am not familiar with that.

Is it possible to build an expression like this
DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED] / 7 * 2 -
Now()-[SCLA]![DATE TO MANGER AND RECEIVED]

Like take the anwser divide by 7 and multiply by 2 and subtract it by
the
total number of days. Simple calculation of adding and subtracting.
Is
it
possible to do that?

I am sorry I am not clear in explaining my situation and formula.
Please
Help in any way.! Thanks in advance! Have a great day!
Click to expand...
Click to expand...
Click to expand...
 
Doh! thanks.

John Vinson said:
The DateDiff function (see Access VBA Help -abbreviated below) will do
exactly what you want.

Your example: DateDiff(["d",[DATE TO MANGER AND RECEIVED] - Date() )
Click to expand...

Small but critical correction, Ed: replace the - with a comma.


John W. Vinson[MVP]
Click to expand...
 
Hi! thanks for your replies and all the information provided!!! I will test
these out! have a nice day!
tina.

Douglas J. Steele said:
Why not either http://www.mvps.org/access/datetime/date0006.htm or
http://www.mvps.org/access/datetime/date0012.htm at "The Access Web", Jeff?

Jeez: Excel indeed! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jeff Boyce said:
Tina

Consider setting a Reference to the Excel object library and using the
NETWORKDAYS() function from Excel.

Good luck!

Jeff Boyce
<Access MVP>

tina said:
Hello thanks for your replies! I've finally got datediff to work, I
tired it
long time before and it did not work the way i wanted so i decided to
just do
a manual caulculation in access, which returned with never ending
decimals
after the number. Right now i got it to show just the number of days!
thanks!

But how Do i get it to calculate the number of days without the weekends?
How do I specify it to calculate monday to friday only? right now I have:

DAYS OUTSTANDING: DateDiff("d",[Date to Manger and received],Date())

This includes weekend and weekdays. I just want the number of days on
the
weekdays. Again, Thanks for your help and detail instructions.

:

The DateDiff function (see Access VBA Help -abbreviated below) will do
exactly what you want.

Your example: DateDiff(["d",[DATE TO MANGER AND RECEIVED] - Date() )

Not sure if you're trying to show the number of biweekly periods, if so,
the
above would be: DateDiff(["ww",[DATE TO MANGER AND RECEIVED] - Date() )

-Ed


==========================================
DateDiff Function
Returns a Variant (Long) specifying the number of time intervals between
two
specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time
you
use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use
in
the calculation.
firstdayofweek Optional. A constant that specifies the first day
of
the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week
of
the year. If not specified, the first week is assumed to be the week in
which January 1 occurs.



Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second


Remarks

You can use the DateDiff function to determine how many specified time
intervals exist between two dates. For example, you might use DateDiff
to
calculate the number of days between two dates, or the number of weeks
between today and the end of the year.

To calculate the number of days between date1 and date2, you can use
either
Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff
returns the number of weeks between the two dates. If date1 falls on a
Monday, DateDiff counts the number of Mondays until date2. It counts
date2
but not date1. If interval is Week ("ww"), however, the DateDiff
function
returns the number of calendar weeks between the two dates. It counts
the
number of Sundays between date1 and date2. DateDiff counts date2 if it
falls
on a Sunday; but it doesn't count date1, even if it does fall on a
Sunday.

If date1 refers to a later point in time than date2, the DateDiff
function
returns a negative number.

========================================

Hello,
I am having trouble calculating the number of days an account is
outstanding (still in process, has not been completed). I consider an
account outstanding if it has been open for more than 10days since the
first
open date. I can calculate the number of dates but i can not get it
to
not
calculate weekend too. Here is what I have so far, something fairly
simply:

DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED]
"Scla" is the table name and "date to manager and received" is the
date of
when the account has been open.

I just want to put in a formula that is simple and if possible no
coding
because I am not familiar with that.

Is it possible to build an expression like this
DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED] / 7 * 2 -
Now()-[SCLA]![DATE TO MANGER AND RECEIVED]

Like take the anwser divide by 7 and multiply by 2 and subtract it by
the
total number of days. Simple calculation of adding and subtracting.
Is
it
possible to do that?

I am sorry I am not clear in explaining my situation and formula.
Please
Help in any way.! Thanks in advance! Have a great day!
Click to expand...
Click to expand...
Click to expand...
 
Doug

So, I suppose you'd use a "home grown" function for calculating Median and
Mode, instead of the already-available Excel functions?!

Jeff <g>

Douglas J. Steele said:
Why not either http://www.mvps.org/access/datetime/date0006.htm or
http://www.mvps.org/access/datetime/date0012.htm at "The Access Web",
Jeff?

Jeez: Excel indeed! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jeff Boyce said:
Tina

Consider setting a Reference to the Excel object library and using the
NETWORKDAYS() function from Excel.

Good luck!

Jeff Boyce
<Access MVP>

tina said:
Hello thanks for your replies! I've finally got datediff to work, I
tired it
long time before and it did not work the way i wanted so i decided to
just do
a manual caulculation in access, which returned with never ending
decimals
after the number. Right now i got it to show just the number of days!
thanks!

But how Do i get it to calculate the number of days without the
weekends?
How do I specify it to calculate monday to friday only? right now I
have:

DAYS OUTSTANDING: DateDiff("d",[Date to Manger and received],Date())

This includes weekend and weekdays. I just want the number of days on
the
weekdays. Again, Thanks for your help and detail instructions.

:

The DateDiff function (see Access VBA Help -abbreviated below) will do
exactly what you want.

Your example: DateDiff(["d",[DATE TO MANGER AND RECEIVED] -
Date() )

Not sure if you're trying to show the number of biweekly periods, if
so, the
above would be: DateDiff(["ww",[DATE TO MANGER AND RECEIVED] - Date() )

-Ed


==========================================
DateDiff Function
Returns a Variant (Long) specifying the number of time intervals
between two
specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time
you
use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use
in
the calculation.
firstdayofweek Optional. A constant that specifies the first day
of
the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first
week of
the year. If not specified, the first week is assumed to be the week in
which January 1 occurs.



Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second


Remarks

You can use the DateDiff function to determine how many specified time
intervals exist between two dates. For example, you might use DateDiff
to
calculate the number of days between two dates, or the number of weeks
between today and the end of the year.

To calculate the number of days between date1 and date2, you can use
either
Day of year ("y") or Day ("d"). When interval is Weekday ("w"),
DateDiff
returns the number of weeks between the two dates. If date1 falls on a
Monday, DateDiff counts the number of Mondays until date2. It counts
date2
but not date1. If interval is Week ("ww"), however, the DateDiff
function
returns the number of calendar weeks between the two dates. It counts
the
number of Sundays between date1 and date2. DateDiff counts date2 if it
falls
on a Sunday; but it doesn't count date1, even if it does fall on a
Sunday.

If date1 refers to a later point in time than date2, the DateDiff
function
returns a negative number.

========================================

Hello,
I am having trouble calculating the number of days an account is
outstanding (still in process, has not been completed). I consider
an
account outstanding if it has been open for more than 10days since
the
first
open date. I can calculate the number of dates but i can not get it
to
not
calculate weekend too. Here is what I have so far, something fairly
simply:

DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED]
"Scla" is the table name and "date to manager and received" is the
date of
when the account has been open.

I just want to put in a formula that is simple and if possible no
coding
because I am not familiar with that.

Is it possible to build an expression like this
DAYS OUTSTANDING: Now()-[SCLA]![DATE TO MANGER AND RECEIVED] / 7 *
2 -
Now()-[SCLA]![DATE TO MANGER AND RECEIVED]

Like take the anwser divide by 7 and multiply by 2 and subtract it by
the
total number of days. Simple calculation of adding and subtracting.
Is
it
possible to do that?

I am sorry I am not clear in explaining my situation and formula.
Please
Help in any way.! Thanks in advance! Have a great day!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top