473,329 Members | 1,302 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,329 software developers and data experts.

Dates should automatically change to weekdays.

I have a report with one of the fields being a planned departure date. The report has an additional field for a cut-off date. The cut-of date is two days prior to the departure date, but should only include weekdays.

=[Planned Departure Date]-2

However, if the planned departure date is on a Monday or Tuesday, then the cut-off date is on a Saturday or Sunday and it should be on the Friday.

How do I have the date automatically change to the Friday?
Sep 22 '15 #1
13 1203
Seth Schrock
2,965 Expert 2GB
You could use the Weekday() function to known if it is a weekend and then perform your calculation accordingly.
Expand|Select|Wrap|Line Numbers
  1. =Switch(WeekDay(([Planned Departure Date] - 2) = 7, [Planned Departure Date] - 3,
  2.         WeekDay(([Planned Departure Date] - 2) = 1, [Planned Departure Date] - 4,
  3.         WeekDay(([Planned Departure Date] - 2) >= 2 And WeekDay(([Planned Departure Date] - 2) <= 6, [Planned Departure Date] - 2
Sep 22 '15 #2
hvsummer
215 128KB
Hi Charl,
use iif will do this better than switch
Expand|Select|Wrap|Line Numbers
  1. iif(weekday([Planned Departure Date]) = 8, [Planned Departure Date]-3,
  2. iif(weekday([Planned Departure Date]) = 9, [Planned Departure Date]-4 , [Planned Departure Date]-2))
  3.  
Sep 22 '15 #3
Seth Schrock
2,965 Expert 2GB
@hvsummer The weekday function will never return 8 or 9, only 1 through 7. Also, the Switch function is actually more preferable than nested IIF()s because of the readability if the functionality would ever need changed. I'm not sure if all parts of the Switch function are evaluated, but I do know that both sides of the IIF function are evaluated, so there is no performance gain from nesting IIF()s.
Sep 22 '15 #4
hvsummer
215 128KB
@seth:
I think iif() can cover the condition better, switch() could lead to duplicate or missing condition for the beginner.
about code, if weekday can't do then use weekdayname lol
Expand|Select|Wrap|Line Numbers
  1. iif(weekdayname([Planned Departure Date]) like "Monday", [Planned Departure Date]-3,
  2. iif(weekdayname([Planned Departure Date]) like "Tuesday", [Planned Departure Date]-4 , [Planned Departure Date]-2))
  3.  
or just cover condition with weekday()= 1 or 2
Expand|Select|Wrap|Line Numbers
  1. iif(weekday([Planned Departure Date]) = 1, [Planned Departure Date]-3,
  2. iif(weekday([Planned Departure Date]) = 2, [Planned Departure Date]-4 , [Planned Departure Date]-2))
  3.  
and btw, Charl Coetzee, you might have to consider the first day of month, when you -2, it could lead to something/value not what you want.
Sep 22 '15 #5
Seth Schrock
2,965 Expert 2GB
The WeekdayName() function will fail as used in your code as it doesn't accept dates as arguments, only integers between 1 and 7 (as would be returned by the Weekday() function). And your second block of code is testing for Sunday and Monday, not Monday and Tuesday (Sunday = 1, Monday = 2, Tuesday = 3...Saturday = 7).

Also, because of how dates are handled, subtracting two will never cause a problem as it isn't subtracting two from the days portion of the date, but from the number value of the date.
Sep 22 '15 #6
hvsummer
215 128KB
@Seth Schrock,
you code is not wrong, but logic's not true.
if weekdayname can't use for number > 7, then just

replace
weekdayname([planned departure date])
by
weekdayname(weekday([planned departure date])) like "Monday" ?

and my 2nd code in last post, showed again below, have already handled all considered condition
Expand|Select|Wrap|Line Numbers
  1. iif(weekday([Planned Departure Date]) = 1, [Planned Departure Date]-3,
  2. iif(weekday([Planned Departure Date]) = 2, [Planned Departure Date]-4 , [Planned Departure Date]-2))
first, let see how it work,
if weekday(P D D) = 1 mean if that day is monday, P.D.D will have to minus 3 to reach last friday (Mon -> sun -> sat -> friday)
then if weekday(PDD) = 2 mean it's tuesday, P.D.D minus 4 will reach last friday (tues -> mon -> sun -> sat -> friday)
then if weekday is not 1 or 2, mean it's from thusday to sunday, it just need to minus 2, never wrong if you cover all the condition
but in case of switch, if you miss 1 condition, the return value will not able to track and fix if you don't remember where it's from.
Sep 23 '15 #7
Seth Schrock
2,965 Expert 2GB
See MS Access: Weekday Function. If Weekday = 1 then that is Sunday, not Monday as I said in my previous post.

However, I did have an error in my code with some missing parenthesis. So here is my corrected code that I have tested:
Expand|Select|Wrap|Line Numbers
  1. Switch(WeekDay(([Planned Departure Date] - 2)) = 7, [Planned Departure Date] - 3, 
  2.       WeekDay(([Planned Departure Date] - 2)) = 1, [Planned Departure Date] - 4, 
  3.       WeekDay(([Planned Departure Date] - 2) >= 2) And WeekDay(([Planned Departure Date] - 2) <= 6), [Planned Departure Date] - 2)
Plug in 9/21/15 (Monday) and it returns 9/18/15 (Friday). Plug in 9/22/15 (Tuesday) and it returns 9/18/15.

You are correct that a Switch function can miss values, but you will notice that my conditions test for 7, 1, 2-6. This covers all possible values that can be returned by the Weekday function.
Sep 23 '15 #8
Seth Schrock
2,965 Expert 2GB
After doing a little bit of research, the Switch function can have the equivalent to the Else statement in a VBA Select Case statement, also known as a default value (from what I've read). This is done by putting the word True as the last conditional expression. So the following returns 9/21/15.
Expand|Select|Wrap|Line Numbers
  1. Switch(WeekDay((#9/23/2015# - 2)) = 7, #9/23/2015# - 3, 
  2.       WeekDay((#9/23/2015# - 2)) = 1, #9/23/2015# - 4, 
  3.       True, #9/23/2015# - 2)
This works because the Switch function returns the first expression that returns true, even though it tests all the expressions.
Sep 23 '15 #9
Seth and Summer. A very warm thanks from Namibia, Africa. I will test both.
Sep 23 '15 #10
hvsummer
215 128KB
@Charl: you welcome
@Seth: nice, that new news to me, else in case, will be valuable next time I use it.
in this situation, just a ez code for charl, he can use whatever he can understand.
if it was me, I'll use
Expand|Select|Wrap|Line Numbers
  1. iif(weekdayname(weekday([Planned Departure Date])) like "monday", [Planned Departure Date]-3,
  2. iif(weekdayname(weekday([Planned Departure Date])) like  "tuesday", [Planned Departure Date]-4 , [Planned Departure Date]-2))
  3.  
ez flow condition, switch() or case()-vba code I think I'll apply for harder situation that "if" condition have to re-write too much time, and too complicate to track source of conditions, or the situation that don't need to cover all conditions.
Sep 23 '15 #11
Well, I can even say thanks for the discussion. I have tried both and through doing so stumbled across a solution for another problem I had.
Sep 24 '15 #12
NeoPa
32,556 Expert Mod 16PB
Bear in mind that the Weekday() function has another parameter - FirstDayOfWeek.

Using that a single use of the function will return what you want/need :
Expand|Select|Wrap|Line Numbers
  1. =DateAdd('d',-IIf(Weekday([Planned Departure Date],2)<3,4,2),[Planned Departure Date])
NB. vbMonday = 2.
Charl didn't explain either that the dates may never start on a Saturday or Sunday (as I suspect to be the case), nor how to handle them if they do. I've assumed that these are days which will never be used for [Planned Departure Date], but the alternative can also be handled if specified.
Sep 24 '15 #13
NeoPa
32,556 Expert Mod 16PB
Forget that last post. I just reread the first post and noticed that I had misread the question. It's not going back two weekdays but instead is going back two days unless that should be to a weekend date, in which case it goes back to the previous Friday.

So instead, we have :
Expand|Select|Wrap|Line Numbers
  1. =DateAdd('d',-IIf(Weekday([Planned Departure Date],2)<3,Weekday([Planned Departure Date],7),2),[Planned Departure Date])
NB. vbMonday = 2; vbSaturday = 7.
Sep 24 '15 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Riley | last post by:
The date fields being saved by a VB program were being saved as #2003-11-22#. For reasons unknown to me these dates began to be saved as "11/22/2003" All of these dates were made dates with the...
8
by: netsurfer | last post by:
Hi: Have a question on making the date automatically filled in by what the user enters in by the date at the top. The date entered at the top would most likely be on a Wednesday then I need...
6
by: Ivan V via DotNetMonster.com | last post by:
Dear All: I have this one stupid that I cannot solve lately. It's all about the textbox. For instance, I have 2 textbox, one is called A and the other is called B. First, I had to input any...
2
by: satnamsarai | last post by:
it is possible to change page orientation to landscape from asp.net page? I am using window.print() dialog to print a page and I would like that page to print automatically in landscape view.
1
by: lupusdark | last post by:
Hi, I have a form which opens another form. when a record is changed on the first form information changes on the second form. The first forms view is on 'single form' but i want the second...
3
by: printline | last post by:
Hello All I need some help in a form i'm creating. I have to fields with drop down select boxes. What i want to do is, when a user selects a given value in one drop down, the other drop down...
0
by: shellegreen | last post by:
Hello, I need help with some password changes that I have to do in some iPAQs. The company that I work has 3 models of iPAQ and 3 different versions of Windows Mobile (Microsoft Pocket PC...
1
by: ppioter | last post by:
hi this regards asp.net 1.1 I have following problem I would like to change attribute of <LINKtag via ASP I have this definition inside <HEADsection of my aspx file <LINK id="stylesheet"...
0
by: Dave Smith | last post by:
I’m looking for a way to have my queries headers change their dates automatically each month or when the date changes. I have a query that exports to an excel file. My query changes automatically...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.