workday

[automatLife84]

Hi, someone have never built a function like the one of excel for workday

[gregster]

I just moved your topic to the more appropriate "Ask for Help" forum. Here, it will get more attention. Besides, "Scripts and Functions" is only for already working, non-trivial scripts and functions, not for requests like this one.

Just to clarify, you are looking for an AHK equivalent of a specific Excel function that returns workdays...?

[jeeswg]

- Here is a prototype WORKDAY equivalent IIRC, but I haven't finalised the lib yet.
- I plan to release a dates lib at some point but it could be many months, even a year. Basically, if people cooperate with me more on my Wish List 2.0, everything will be faster. I have dozens of libs/scripts to release.

Code: Select all

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

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

;Excel WORKDAY Function
;https://www.excelfunctions.net/excel-workday-function.html

;similar to Excel's WORKDAY function
JEE_DateAddWorkdays(vDate, vNum, vListWDay:=23456, vListExclude:="", vFormat:="yyyyMMddHHmmss")
{
	local
	if !vNum
		return vDate
	vOffset := (vNum > 0) ? 1 : -1
	vNum := Abs(vNum)-1
	vDate1 := FormatTime(DateAdd(vDate, vOffset, "D"), "yyyyMMdd")
	;full weeks + some days are needed
	vCountW := vNum // StrLen(vListWDay)
	vCountD2 := 1+Mod(vNum, StrLen(vListWDay))
	oArray := []
	if !(vListExclude = "")
	{
		vListExclude := JEE_DateCleanList(vListExclude)
		vListExclude := Sort(vListExclude, "D, N" (vOffset=1?" R":""))
		Loop, Parse, vListExclude, % ","
			if ((vOffset = 1) && (A_LoopField >= vDate1))
			|| ((vOffset = -1) && (A_LoopField <= vDate1))
				oArray.Push(A_LoopField)
	}
	vWDay := FormatTime(vDate, "WDay")
	vCount := 0
	Loop
	{
		if !vCountD2
			break
		Loop
		{
			vWDay := Mod(vWDay+6+vOffset, 7) + 1
			vCount++
			if InStr(vListWDay, vWDay)
				vCountD2--
			if !vCountD2
				break
		}
		vDate2X := DateAdd(vDate, vOffset*(vCountW*7+vCount), "D")
		vDate2 := SubStr(vDate2X, 1, 8)
		if !oArray.Length()
			break
;MsgBox, % vDate1 "`r`n" oArray[oArray.Length()] "`r`n" vDate2
		Loop
		{
			if !oArray.Length()
				break
			vTemp := oArray[oArray.Length()]
			if !((vOffset = 1) && (vTemp >= vDate1) && (vTemp <= vDate2))
			&& !((vOffset = -1) && (vTemp <= vDate1) && (vTemp >= vDate2))
				break
			oArray.Pop()
			if InStr(vListWDay, FormatTime(vTemp, "WDay"))
				vCountD2 += 1
		}
;MsgBox, % vCountD2
	}
	if !(vFormat == "yyyyMMddHHmmss")
		return FormatTime(vDate, vFormat)
	return vDate2X
}

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

JEE_DateCleanList(vList, vLen:=8)
{
	local
	vList2 := ""
	if !IsObject(vList)
		vList := StrSplit(vList, ",")
	for _, vDate in vList
	{
		if !(StrLen(vDate) = vLen)
			vDate := FormatTime(vDate, SubStr("yyyyMMddHHmmss", 1, vLen))
		vList2 .= vDate ","
	}
	vList2 := SubStr(vList2, 1, -1)
	vList2 := Sort(vList2, "D, U")
	return vList2
}

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

[automatLife84]

I just moved your topic to the more appropriate "Ask for Help" forum. Here, it will get more attention. Besides, "Scripts and Functions" is only for already working, non-trivial scripts and functions, not for requests like this one.

Just to clarify, you are looking for an AHK equivalent of a specific Excel function that returns workdays...?

yes,i'm looking for an AHK equivalent of a specific Excel function that returns workdays

thanks in advance

[AlphaBravo]

Code: Select all

MsgBox % WorkDay(20190322, 2, [20190326, 20190327])
MsgBox % WorkDay(20190322, 2)
return

WorkDay(Start_Date, Days, Holidays := ""){
	WorkDay_Loop:
	while Days
	{
		Start_Date++
		FormatTime, WeekDay , % Start_Date, WDay
		if WeekDay in 1,7
			continue
		for each, Holiday in Holidays
			if (Start_Date = Holiday)
				continue, WorkDay_Loop
		Days--
	}
	return Start_Date
}

[jeeswg]

Btw @AlphaBravo: Start_Date++,
should probably be: Start_Date += 1, Days or EnvAdd, Start_Date, 1, Days.