date calculation

[john511178]

Hello. I search these boards frequently, but this time I had to register and ask for help.

If this is the wrong place, please let me know the proper place to ask.

I am not a proficient AHK user, but I can usually fight my way through simple scripts I write.
I am trying to calculate the difference between two dates (in days). One being the input from a msgbox in a text string (eg. 9/18 or 10/22) and the other is the current date using the A_now function. I don't know how to convert the string into a date format from which I can subtract the current date.

I've spent hours trying different methods I found in the forums with no success. I'm sure this is simple to someone who knows what they are doing.
I know a little, but don't assume I know something that may seem simple. I'm pulling my hair out, so please provide actual script if possible. The parameters of the some functions gets confusing.

Thanks, John

[Rohwedder]

Hallo,
try:

Code: Select all

Inputbox, Date, Month/Day
StringSplit, Date, Date, /,
Date := A_Year Format("{:02}", Date1) Format("{:02}", Date2)
Now := A_Year A_Mon A_DD
EnvSub, Date, %Now%, days
MsgBox, % Date " days"

[jeeswg]

Here's a similar example.

Code: Select all

q:: ;get date difference in days
vDate1 := "9/18"
;vDate1 := "10/22"
oTemp := StrSplit(vDate1, "/")
vDate1 := A_Year Format("{:02}{:02}", oTemp.1, oTemp.2)
vDate2 := SubStr(A_Now, 1, 8)
vDateDiff := vDate1
EnvSub, vDateDiff, % vDate2, D
MsgBox, % vDateDiff " days"
return

[DRocks]

Very interesting.. I did not hear about this function (envsub) before even when I was looking for date calculations...

Could you find what Quarter a business is in comparing the beginning of its fiscal.year?
Example: company A incorporated begins fiscal year 2018-02-01. Todays date 2018-09-14.

Could you calculate in what Quarter of its fiscal year the company is currently? Ive tried to do that before but since date dont calculate like regular numbers I couldnt figure out. My maths is weak

[jeeswg]

- I would do something like this for calculating date to quarter.
- The maths for 'years' not starting in January came out a little fiddly.
- Interesting challenge. Cheers.

Code: Select all

q:: ;date get quarter
vDate := A_Now
vMonth := SubStr(vDate, 5, 2)

;where Q1 starts in Jan
MsgBox, % JEE_MonthToQuarter(vMonth)
vOutput := ""
Loop, 12
	vOutput .= A_Index "`t" JEE_MonthToQuarter(A_Index) "`r`n"
MsgBox, % vOutput

;where Q1 starts in Feb
MsgBox, % JEE_MonthToQuarter(vMonth, 2)
vOutput := ""
Loop, 12
	vOutput .= A_Index "`t" JEE_MonthToQuarter(A_Index, 2) "`r`n"
MsgBox, % vOutput

;change the first month of Q1 each time
Loop, 12
{
	vMonthFirst := A_Index
	vOutput := ""
	Loop, 12
		vOutput .= A_Index "`t" JEE_MonthToQuarter(A_Index, vMonthFirst) "`r`n"
	MsgBox, % vOutput
}
return

;==================================================

;where vMonth and vMonthFirst are between 1 and 12
;where vMonthFirst is the first month of the 'year'
;returns a number between 1 and 4
JEE_MonthToQuarter(vMonth, vMonthFirst:=1)
{
	return Floor((Mod(vMonth+12-vMonthFirst, 12) + 3)/3)
}

;1/2/3 -> 1, 4/5/6 -> 2, 7/8/9 -> 3, 10/11/12 -> 4
JEE_MonthToQuarterSimple(vMonth)
{
	return Floor((vMonth + 2)/3)
}

- You can use EnvSub or -= with an extra parameter.
- EnvSub is easier to convert to AHK v2 so I use that.
- You can use DateDiff in AHK v2, or a custom backport of it in AHK v1.
commands as functions (AHK v2 functions for AHK v1) - AutoHotkey Community
https://autohotkey.com/boards/viewtopic ... 37&t=29689

[DRocks]

nice!! I would never have thought of using vMonthFirst, Floor and Mod maths!
Thanks alot for all this. I'll be experimenting with it and trying to understand more completely the process to get the vOutput

[DRocks]

Hello dear jeeswg


I have finally come to a point where I am using your function JEE_MonthToQuarter to find what quarter I am in with my first script project.
And now I want to expand beyond this but date calculation is something that really makes my brain want to explose and so I risk myself of asking you for some guidance if you please will...

...If this is interesting to you, please look at this situation:
I am trying to do these things:
1. Know what quarter I am currently in depending on FirstMonth of Year and Current month (solved perfectly by your function - thanks again)
2. Know what is the period of that current Quarter. (what are the 3 months, what is the last day of this quarter)
For example:

As you can see in the picture, there is a error with the period, its not the right answer.
Here's my code attempt:

Code: Select all

Period(FirstMonth, CurrrentQuarter){
	if (FirstMonth=1){
		if (CurrrentQuarter=1)
			Trimestre:=["Janvier", "Février", "Mars"], YYYYofLastMonth:=SubStr(A_Now,1,4), LastMonth:=03
		if (CurrrentQuarter=2)
			Trimestre:=["Avril", "Mai", "Juin"], YYYYofLastMonth:=SubStr(A_Now,1,4), LastMonth:=06
		if (CurrrentQuarter=3)
			Trimestre:=["Juillet", "Août", "Septembre"], YYYYofLastMonth:=SubStr(A_Now,1,4), LastMonth:=09
		if (CurrrentQuarter=4)
			Trimestre:=["Octobre", "Novembre", "Décembre"], YYYYofLastMonth:=SubStr(A_Now,1,4), LastMonth:=12
	}
	if (FirstMonth=2){
		if (CurrrentQuarter=1)
			Trimestre:=["Février", "Mars", "Avril"]
		if (CurrrentQuarter=2)
			Trimestre:=["Mai", "Juin", "Juillet"]
		if (CurrrentQuarter=3)
			Trimestre:=["Août", "Septembre", "Octobre"]
		if (CurrrentQuarter=4)
			Trimestre:=["Novembre", "Décembre", "Janvier"], YYYYofLastMonth:=SubStr(A_Now,1,4), LastMonth:=12
	}
	
	return Taxes_MoisDuTrimestre:= "1st " Trimestre[1] " to " LDOM(YYYYofLastMonth LastMonth) " " Trimestre[3] " " YYYYofLastMonth
}

;--------------------------------------------------------------------------------
;https://autohotkey.com/board/topic/7984-ahk-functions-incache-cache-list-of-recent-items/#entry51188
LDOM(yyyyMM) {
;--------------------------------------------------------------------------------
	StringLeft Date,yyyyMM,6 ; YearMonth
	Date1 = %Date%
	Date1+= 31,D              ; A day in next month
	StringLeft Date1,Date1,6  ; YearNextmonth
	Date1-= %Date%,D          ; Difference in days
	Return Date1
}

I have a big problem with how to know what is the last day of this quarter because sometimes a quarter can satrt in 2018 and end in 2019 - if your FirstMonth happens to be like Febuary for example
In this example your period would be from 2018-11-01 to 2019-01-31

Of course you wont do the job for me but do you have any clue that could help me think more clearly i'm really confused and feel desperated about this.
Thanks in advance and please don't waste your precious time if you don't feel like it



Your original function:

- I would do something like this for calculating date to quarter.
- The maths for 'years' not starting in January came out a little fiddly.
- Interesting challenge. Cheers.

Code: Select all

q:: ;date get quarter
vDate := A_Now
vMonth := SubStr(vDate, 5, 2)

;where Q1 starts in Jan
MsgBox, % JEE_MonthToQuarter(vMonth)
vOutput := ""
Loop, 12
	vOutput .= A_Index "`t" JEE_MonthToQuarter(A_Index) "`r`n"
MsgBox, % vOutput

;where Q1 starts in Feb
MsgBox, % JEE_MonthToQuarter(vMonth, 2)
vOutput := ""
Loop, 12
	vOutput .= A_Index "`t" JEE_MonthToQuarter(A_Index, 2) "`r`n"
MsgBox, % vOutput

;change the first month of Q1 each time
Loop, 12
{
	vMonthFirst := A_Index
	vOutput := ""
	Loop, 12
		vOutput .= A_Index "`t" JEE_MonthToQuarter(A_Index, vMonthFirst) "`r`n"
	MsgBox, % vOutput
}
return

;==================================================

;where vMonth and vMonthFirst are between 1 and 12
;where vMonthFirst is the first month of the 'year'
;returns a number between 1 and 4
JEE_MonthToQuarter(vMonth, vMonthFirst:=1)
{
	return Floor((Mod(vMonth+12-vMonthFirst, 12) + 3)/3)
}

;1/2/3 -> 1, 4/5/6 -> 2, 7/8/9 -> 3, 10/11/12 -> 4
JEE_MonthToQuarterSimple(vMonth)
{
	return Floor((vMonth + 2)/3)
}

- You can use EnvSub or -= with an extra parameter.
- EnvSub is easier to convert to AHK v2 so I use that.
- You can use DateDiff in AHK v2, or a custom backport of it in AHK v1.
commands as functions (AHK v2 functions for AHK v1) - AutoHotkey Community
https://autohotkey.com/boards/viewtopic ... 37&t=29689

[jeeswg]

@DRocks: Something like this perhaps. It can handle positive quarter values, I might come back and have a look at 0/negative values also. Cheers.

Code: Select all

q:: ;quarter - get start/end dates
vOutput := ""
vMonthFirst := 1
;vMonthFirst := 3
vYear := A_YYYY
;vYear := A_YYYY+1
vOffset := 0
Loop, 4
	vOutput .= JEE_QuarterGetStart(A_Index+vOffset, vMonthFirst, vYear) "-"
	, vOutput .= JEE_QuarterGetEnd(A_Index+vOffset, vMonthFirst, vYear) "`r`n"
MsgBox, % vOutput
return

;note: vQuarter: an integer between 1 and 4 (or higher)
;note: vMonthFirst: an integer between 1 and 12
JEE_QuarterGetStart(vQuarter, vMonthFirst:=1, vYear:="")
{
	local
	if (vYear = "")
		vYear := A_YYYY
	vMonth := vMonthFirst+(vQuarter-1)*3
	if (vMonth < 1)
		return
	if (vMonth > 12)
		vYear += (vMonth-1) // 12
		, vMonth := Mod(vMonth-1, 12)+1
	return vYear Format("{:02}", vMonth) "00000000"
}

;note: vQuarter: an integer between 1 and 4 (or higher)
;note: vMonthFirst: an integer between 1 and 12
JEE_QuarterGetEnd(vQuarter, vMonthFirst:=1, vYear:="")
{
	local
	if (vYear = "")
		vYear := A_YYYY
	vMonth := vMonthFirst+vQuarter*3
	if (vMonth < 1)
		return
	if (vMonth > 12)
		vYear += (vMonth-1) // 12
		, vMonth := Mod(vMonth-1, 12)+1
	vDate := vYear Format("{:02}", vMonth)
	return DateAdd(vDate, -1, "S")
}

/*
;commands as functions (AHK v2 functions for AHK v1) - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=37&t=29689

DateAdd(DateTime, Time, TimeUnits)
{
	EnvAdd DateTime, %Time%, %TimeUnits%
	return DateTime
}
*/

[EDIT:] Changed to subtract 1 second instead of 1 day, for the true end of the quarter.

[DRocks]

@jeeswg ok wow this is much less code lines and complexification than I was trying to do. I think I was doing this because visual table helped me out to figure things.

Your functions do return accurate year and even the right last day of ending month. This is impressive and very satisfying! I will indeed test more and try integrating it tommorow morning but as for my pre-sleep tests its awesome!

Thanks a lot! !

[DRocks]

@DRocks: Something like this perhaps. It can handle positive quarter values, I might come back and have a look at 0/negative values also. Cheers.
[EDIT:] Changed to subtract 1 second instead of 1 day, for the true end of the quarter.

Hello again dear jeeswg,

I have been testing and integrating your functions to my GUI - and I want to share it with you for others to use also.
Formating as been changed for my personnal use, as you'll see in the functions you made.

I have noticed a problem when no year is given to the functions - if for example we are currently in January 2019 but the fiscal year started in december 2018.
If vYear is empty when calling the functions it will default to december 2019 as the start but it logically needs to be 2018 since we're not already in december 2019 at this current time.
How I am reasoning to sort this out is that if current Month is smaller than FirstMonth of fiscal year, then A_YYYY must be subtracted by 1 to go back to where it should be.

Please let me know what you think.
Thank you again VERY MUCH

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance, Force ; Prevent double execution of this script
Gui, Color, c454545
Gui, Margin, 30, 10

;-- Current Quarter
Gui, Font, cWhite bold s8
CurMonth:=A_MM,CurQuarter:=JEE_MonthToQuarter(CurMonth,FirstMonth), CurYear:=(A_MM < FirstMonth ? A_YYYY-1:"")
Gui, Add, Text, % "xm y+50 +0x4000 vCurQuarter", % "CURRENT QUARTER: " CurQuarter
Gui, Font
;-- First month of fiscal year
Gui, Font, cWhite s8
Gui, Add, Text, % "x+m yp +0x4000", First month of fiscal year:
Gui, Font, cBlack s8
Gui, Add, DropDownList, % "x+5 yp-4 w78 vFirstMonth gFirstMonth r12 AltSubmit", January||Febuary|March|April|May|June|Jully|August|September|October|November|December
GuiControl,Choose,FirstMonth,%FirstMonth%
Gui, Add, picture,x+7 yp-9, Images\GUI_CalendarIcon.png
;-- Quarter periods
Gui, Font, cWhite s8
Gui, Add, Text, % "xm+30 y+18 w300 +0x4000 vQuarter1"
Gui, Add, Text, % "xp y+2 w300 +0x4000 vQuarter2"
Gui, Add, Text, % "xp y+2 w300 +0x4000 vQuarter3"
Gui, Add, Text, % "xp y+2 w300 +0x4000 vQuarter4"
Gui, Add, picture,% "x32 y1 w6 h2 vImgWhitePixel", Images\WhitePixel.png
Gui, Font 

Gui, show

FirstMonth:
GuiControlGet, FirstMonth
CurMonth:=A_MM,CurQuarter:=JEE_MonthToQuarter(CurMonth,FirstMonth), CurYear:=(A_MM < FirstMonth ? A_YYYY-1:"")
GuiControl,,CurQuarter,% "CURRENT QUARTER: " CurQuarter
GuiControl,,Quarter1,% "Quarter 1:   " JEE_QuarterGetStart(1, FirstMonth, CurYear) "  to  " JEE_QuarterGetEnd(1, FirstMonth, CurYear)
GuiControl,,Quarter2,% "Quarter 2:   " JEE_QuarterGetStart(2, FirstMonth,CurYear) "  to  " JEE_QuarterGetEnd(2, FirstMonth,CurYear)
GuiControl,,Quarter3,% "Quarter 3:   " JEE_QuarterGetStart(3, FirstMonth,CurYear) "  to  " JEE_QuarterGetEnd(3, FirstMonth,CurYear)
GuiControl,,Quarter4,% "Quarter 4:   " JEE_QuarterGetStart(4, FirstMonth,CurYear) "  to  " JEE_QuarterGetEnd(4, FirstMonth,CurYear)
GuiControlGet,Pos,Pos,Quarter%CurQuarter%
GuiControl,Move,ImgWhitePixel,% "y"PosY+6
return


;--------------------------------------------------------------------------------
;where vMonth and vMonthFirst are between 1 and 12
;where vMonthFirst is the first month of the 'year'
;returns a number between 1 and 4
JEE_MonthToQuarter(vMonth, vMonthFirst:=1) {
;--------------------------------------------------------------------------------
	return Floor((Mod(vMonth+12-vMonthFirst, 12) + 3)/3)
}
;note: vQuarter: an integer between 1 and 4 (or higher)
;note: vMonthFirst: an integer between 1 and 12
JEE_QuarterGetStart(vQuarter, vMonthFirst:=1, vYear:="")
{
	local
	;--1--
	if (vYear = "")
		vYear:= A_YYYY
	vMonth:= vMonthFirst+(vQuarter-1)*3
	if (vMonth < 1)
		return
	if (vMonth > 12)
		vYear += (vMonth-1) // 12, vMonth:= Mod(vMonth-1, 12)+1
	;--2--
	QuarterStart:=vYear Format("{:02}", vMonth) "01000000"
	FormatTime,QuarterStart,%QuarterStart%,yyyy-MM-dd ; ShortDate
	return QuarterStart
}
JEE_QuarterGetEnd(vQuarter, vMonthFirst:=1, vYear:="")
{
	local
	if (vYear = "")
		vYear:= A_YYYY
	vMonth:= vMonthFirst+vQuarter*3
	if (vMonth < 1)
		return
	if (vMonth > 12)
		vYear += (vMonth-1) // 12, vMonth:= Mod(vMonth-1, 12)+1
	QuarterEnd:= vYear Format("{:02}", vMonth)
	EnvAdd QuarterEnd, -1, S
	FormatTime,QuarterEnd,%QuarterEnd%,yyyy-MM-dd ; Short Date
	return QuarterEnd
}


GuiEscape:
ExitApp

JEE_YearQuarters.zip file with 2 images for GUI example:
https://1drv.ms/u/s!AnxboyZ6ZdpRg6oaVGFvTSEw8IOoKQ