473,322 Members | 1,345 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,322 software developers and data experts.

fiscal quarters based on different fiscal start dates

2
I am creating grants tracking database,where grants have different Fiscal year start and end dates, users select the month drop down list to enter act,bud data. I need the quarters field to be calculated based on fiscal year start dte and month from the drop down list. I am a novice in VBA, so please go easy on me. Thanks.
Attached Images
File Type: jpg image001-1.jpg (14.3 KB, 169 views)
Sep 15 '15 #1
4 1480
jforbes
1,107 Expert 1GB
I'm not exactly sure what you are attempting so I thought I would share some of the Date functions that I use:
Expand|Select|Wrap|Line Numbers
  1. ' ==============
  2. ' Date Functions
  3. ' ==============
  4. Public Function getDaysFromNowDate(ByRef iDays As Integer) As Date
  5.     getDaysFromNowDate = DateAdd("d", iDays, Now())
  6. End Function
  7. Public Function getDaysFromNowDateStr(ByRef iDays As Integer) As String
  8.     getDaysFromNowDateStr = Format(getDaysFromNowDate(iDays), "mm/dd/yyyy")
  9. End Function
  10. Public Function getFirstOfTheYear() As Date
  11.     getFirstOfTheYear = DateSerial(Year(Date), 1, 1)
  12. End Function
  13. Public Function getFirstOfTheYearStr() As String
  14.     getFirstOfTheYearStr = Format(getFirstOfTheYear(), "mm/dd/yyyy")
  15. End Function
  16. Public Function getFirstOfFiscalYear() As Date
  17.     If Month(Now) >= 10 Then
  18.         getFirstOfFiscalYear = DateSerial(Year(Date), 10, 1)
  19.     Else
  20.         getFirstOfFiscalYear = DateSerial(Year(Date) - 1, 10, 1)
  21.     End If
  22. End Function
  23. Public Function getFirstOfFiscalYearStr() As String
  24.     getFirstOfFiscalYearStr = Format(getFirstOfFiscalYear(), "mm/dd/yyyy")
  25. End Function
  26. Public Function getEndOfFiscalYear() As Date
  27.     If Month(Now) >= 10 Then
  28.         getEndOfFiscalYear = DateSerial(Year(Date) + 1, 10, 1) - 1
  29.     Else
  30.         getEndOfFiscalYear = DateSerial(Year(Date), 10, 1) - 1
  31.     End If
  32. End Function
  33. Public Function getFirstOfQuater(ByRef iQuater As Integer) As Date
  34.     getFirstOfQuater = DateAdd("m", (iQuater - 1) * 3, getFirstOfTheYear())
  35. End Function
  36. Public Function getFirstOfFiscalQuater(ByRef iQuater As Integer) As Date
  37.     getFirstOfFiscalQuater = DateAdd("m", (iQuater - 1) * 3, getFirstOfFiscalYear())
  38. End Function
  39. Public Function getDaysThisYear() As Integer
  40.     getDaysThisYear = Format(Date, "y")
  41. End Function
  42. Public Function getDayOfWeek(ByRef iDay As Integer) As String
  43.     Select Case iDay Mod 7
  44.         Case 1
  45.             getDayOfWeek = "Sunday"
  46.         Case 2
  47.             getDayOfWeek = "Monday"
  48.         Case 3
  49.             getDayOfWeek = "Tuesday"
  50.         Case 4
  51.             getDayOfWeek = "Wednesday"
  52.         Case 5
  53.             getDayOfWeek = "Thurday"
  54.         Case 6
  55.             getDayOfWeek = "Friday"
  56.         Case 0
  57.             getDayOfWeek = "Saturday"
  58.         Case Else
  59.             getDayOfWeek = "You are currently time traveling"
  60.     End Select
  61. End Function
  62. Public Function getDaysInMonth(ByRef dDate As Date) As Integer
  63.     getDaysInMonth = DateDiff("d", dDate, DateAdd("m", 1, dDate))
  64. End Function
  65.  
These functions are a work in progress and are pretty incomplete, but I thought it would be more beneficial for you to have them so you can see how to go about some basic Date calculations. I think getFirstOfFiscalYear() and getFirstOfFiscalQuater() are similar to what you are looking for. They have the cutoff Month for the Fiscal Year hardcoded since that isn't something that changes in our company often, but it looks like that may need to be a parameter for your situation. Again, I'm not 100% sure what you are attempting to accomplish after the user selects the Month.
Sep 15 '15 #2
Rabbit
12,516 Expert Mod 8TB
Use the DateDiff function to calculate the number of months from the fiscal start date to the chosen date that you want to calculate the quarter for. Take that number and divide by 3, dropping the fraction. Then add 1. That gives you the fiscal quarter.
Sep 15 '15 #3
syedi
2
So what I want is for the user to select month from the drop down, and based on the fiscal year already chosen, the quarter field is populated with the correct quarter. So the combination of fiscal year and month chosen should default to the correct quarter in the quarter field. Thanks.
Sep 15 '15 #4
Rabbit
12,516 Expert Mod 8TB
That's what the algorithm I outlined in post #3 will do.
Sep 15 '15 #5

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

Similar topics

2
by: joao coelho | last post by:
We have a character based application that runs on unix. We want to be able to run the app both on unix and windows, so we thought about using java but as it happens since our app also must be...
7
by: DavidM | last post by:
Hello -- I would like to display a different logo on my website for different seasons of the month. I currently have a logo for Halloween, Thanksgiving, and December. Can someone tell me what...
12
by: Steve Elliott | last post by:
I have a query set up to gather together data between two specified dates. Shown in the query column as: Between #24/09/2004# And #01/10/2004# Is it possible to enter several different date...
3
by: haydn_llewellyn | last post by:
Hi, My company runs on a fiscal calendar that starts on the first monday in July, and is based on a 13 week quarter (4 weeks, 4 weeks, 5 weeks). What I need, is a way of relating Date() to the...
16
by: Serdar Kalaycý | last post by:
Hi everybody, My problem seems a bit clichè but I could not work around. Well I read lots of MSDN papers and discussions, but my problem is a bit different from them. When I tried to run the...
17
by: romixnews | last post by:
Hi, I'm facing the problem of analyzing a memory allocation dynamic and object creation dynamics of a very big C++ application with a goal of optimizing its performance and eventually also...
4
by: Good Man | last post by:
Hi there I have a list of jobs scheduled in a MySQL table, with start dates and end dates, like so: SchedID | JobID | StartDate | EndDate |...
1
by: Christina123 | last post by:
All: I have a database that tracks the withdraws and returns of shared tools. It has been requested that the datablase is made able to show all orders based on a date range. My orders only...
0
by: badarinarayan | last post by:
CrystalReportViewer1.SelectionFormula = "{tbl_srvmast1.Com_Type}='" + DropDownList1.SelectedItem.Text + "'" + "AND {tbl_srvmast1.Com_Date}>='#"&TextBox3.Text&"#" + "AND {tbl_srvmast1.Com_Date}<=...
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
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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.