If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DateSerial formula help
Hello, I cant figure this out! I want to use the DateSerial to find these
date ranges. I searched all over the net/forums and really didnt get an answer. I need to find the first and last days of the previous year and I also need to find the first day (Sunday) and last day (saturday) of the previous week. I had found this Ms site (Very Helpfull) but nothing on years... http://support.microsoft.com/kb/210604 |
#2
|
|||
|
|||
DateSerial formula help
Since every year starts on January 1, and ends on December 31, the first day
of the previous year is DateSerial(Year(Date) - 1, 1, 1) and the last day of the previous year is DateSerial(Year(Date) - 1, 12, 31) For the second part of your question, you can use the Weekday function to determine the week day of the current day, and then subtract the appropriate number of days from the current day once you know what weekday it is. For instance, today (02 Mar, 2008) is a Sunday (Weekday(Date) = 1). That means to determine the Sunday of the previous week, you'd need to subtract 7 from today. Similarly, tomorrow (03 Mar, 2008) will be Monday, so you'd need to subtract 8 days to get the Sunday of the previous week. You can use the DateSerial function: DateSerial(Year(Date), Month(Date), Day(Date) - 6 - Weekday(Date)) but it's actually better to use the DateAdd function: DateAdd("d", -6 - Weekday(Date), Date) Hopefully it's apparent that the previous Saturday can be calculated as DateAdd("d", -Weekday(Date), Date) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Chad" wrote in message ... Hello, I cant figure this out! I want to use the DateSerial to find these date ranges. I searched all over the net/forums and really didnt get an answer. I need to find the first and last days of the previous year and I also need to find the first day (Sunday) and last day (saturday) of the previous week. I had found this Ms site (Very Helpfull) but nothing on years... http://support.microsoft.com/kb/210604 |
#3
|
|||
|
|||
DateSerial formula help
Thank you so much! Do you have a good reading resources on finding date
ranges using the DateSerial and DateAdd? Thanks again! -- Newbies need extra loven......... "Douglas J. Steele" wrote: Since every year starts on January 1, and ends on December 31, the first day of the previous year is DateSerial(Year(Date) - 1, 1, 1) and the last day of the previous year is DateSerial(Year(Date) - 1, 12, 31) For the second part of your question, you can use the Weekday function to determine the week day of the current day, and then subtract the appropriate number of days from the current day once you know what weekday it is. For instance, today (02 Mar, 2008) is a Sunday (Weekday(Date) = 1). That means to determine the Sunday of the previous week, you'd need to subtract 7 from today. Similarly, tomorrow (03 Mar, 2008) will be Monday, so you'd need to subtract 8 days to get the Sunday of the previous week. You can use the DateSerial function: DateSerial(Year(Date), Month(Date), Day(Date) - 6 - Weekday(Date)) but it's actually better to use the DateAdd function: DateAdd("d", -6 - Weekday(Date), Date) Hopefully it's apparent that the previous Saturday can be calculated as DateAdd("d", -Weekday(Date), Date) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Chad" wrote in message ... Hello, I cant figure this out! I want to use the DateSerial to find these date ranges. I searched all over the net/forums and really didnt get an answer. I need to find the first and last days of the previous year and I also need to find the first day (Sunday) and last day (saturday) of the previous week. I had found this Ms site (Very Helpfull) but nothing on years... http://support.microsoft.com/kb/210604 |
#4
|
|||
|
|||
DateSerial formula help
I'm not aware of any resource, but both functions are fairly
straight-forward. DateAdd adds (or subtracts) a specific time interval to a given date. DateSerial requires three arguments: year, month and day. When any argument exceeds the accepted range for that argument, it increments to the next larger unit as appropriate. For example, if you specify 35 days, it is evaluated as one month and some number of days, depending on where in the year it is applied. If any single argument is outside the range -32,768 to 32,767, an error occurs. If the date specified by the three arguments falls outside the acceptable range of dates, an error occurs. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Chad" wrote in message news Thank you so much! Do you have a good reading resources on finding date ranges using the DateSerial and DateAdd? Thanks again! -- Newbies need extra loven......... "Douglas J. Steele" wrote: Since every year starts on January 1, and ends on December 31, the first day of the previous year is DateSerial(Year(Date) - 1, 1, 1) and the last day of the previous year is DateSerial(Year(Date) - 1, 12, 31) For the second part of your question, you can use the Weekday function to determine the week day of the current day, and then subtract the appropriate number of days from the current day once you know what weekday it is. For instance, today (02 Mar, 2008) is a Sunday (Weekday(Date) = 1). That means to determine the Sunday of the previous week, you'd need to subtract 7 from today. Similarly, tomorrow (03 Mar, 2008) will be Monday, so you'd need to subtract 8 days to get the Sunday of the previous week. You can use the DateSerial function: DateSerial(Year(Date), Month(Date), Day(Date) - 6 - Weekday(Date)) but it's actually better to use the DateAdd function: DateAdd("d", -6 - Weekday(Date), Date) Hopefully it's apparent that the previous Saturday can be calculated as DateAdd("d", -Weekday(Date), Date) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Chad" wrote in message ... Hello, I cant figure this out! I want to use the DateSerial to find these date ranges. I searched all over the net/forums and really didnt get an answer. I need to find the first and last days of the previous year and I also need to find the first day (Sunday) and last day (saturday) of the previous week. I had found this Ms site (Very Helpfull) but nothing on years... http://support.microsoft.com/kb/210604 |
#5
|
|||
|
|||
DateSerial formula help
Chad,
This article is great for displaying specific dates: http://support.microsoft.com/kb/210604 "Chad" wrote: Thank you so much! Do you have a good reading resources on finding date ranges using the DateSerial and DateAdd? Thanks again! -- Newbies need extra loven......... "Douglas J. Steele" wrote: Since every year starts on January 1, and ends on December 31, the first day of the previous year is DateSerial(Year(Date) - 1, 1, 1) and the last day of the previous year is DateSerial(Year(Date) - 1, 12, 31) For the second part of your question, you can use the Weekday function to determine the week day of the current day, and then subtract the appropriate number of days from the current day once you know what weekday it is. For instance, today (02 Mar, 2008) is a Sunday (Weekday(Date) = 1). That means to determine the Sunday of the previous week, you'd need to subtract 7 from today. Similarly, tomorrow (03 Mar, 2008) will be Monday, so you'd need to subtract 8 days to get the Sunday of the previous week. You can use the DateSerial function: DateSerial(Year(Date), Month(Date), Day(Date) - 6 - Weekday(Date)) but it's actually better to use the DateAdd function: DateAdd("d", -6 - Weekday(Date), Date) Hopefully it's apparent that the previous Saturday can be calculated as DateAdd("d", -Weekday(Date), Date) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Chad" wrote in message ... Hello, I cant figure this out! I want to use the DateSerial to find these date ranges. I searched all over the net/forums and really didnt get an answer. I need to find the first and last days of the previous year and I also need to find the first day (Sunday) and last day (saturday) of the previous week. I had found this Ms site (Very Helpfull) but nothing on years... http://support.microsoft.com/kb/210604 |
Thread Tools | |
Display Modes | |
|
|