Adding Decimals Topic is solved

[AlFlo]

I am writing down the time I spend for clients in Word documents. So I type a table in Word like this:

Timesheet Screenshot 1.png (34.36 KiB) Viewed 962 times

I want a script where, after I highlight the time entries (which are decimals) with my mouse:

Timesheet Screenshot 2.png (34.37 KiB) Viewed 962 times

The AHK script gives me a total (in this case, the total is 1.3 i.e. .6+.5+.2 = 1.3).

How can I modify this script which totals WHOLE numbers, but doesn't recognize decimals (the script just strips them out or ignores them), to instead add up the decimals I've highlighted?:

Code: Select all

Sum := 0
Send % ("^c", Clipboard := "")
ClipWait, 0
String := Clipboard
for x,y in strsplit(clipboard,"`n")
ttl += regexreplace(regexreplace(y,"[^\d.-]"),"^[^0-9-]*")
ttl := round(ttl,2)
Sleep, 2000
Send {Down}
Send Subtotal: 
Send {Space}{Space} 
Send % RegExReplace(ttl, "(?(?<=\.)(*COMMIT)(*FAIL))\d(?=(\d{3})+(\D|$))", "$0,")
return

[AlFlo]

Never mind, I've got it:

Code: Select all

for x,y in strsplit(clipboard,"`n")
	ttl += regexreplace(y,"[^\d.]")
MsgBox % ttl := round(ttl,1)
Return

[FanaticGuru]

I am writing down the time I spend for clients in Word documents. So I type a table in Word like this:

People want what they want but I thought I would just mention that Word will total columns in tables. You can put formulas in a table kind of like a simplified Excel.

FG

[AlFlo]

FG,

Thanks ... I didn't know that!

[AlFlo]

FG,

Do you know if you can do math on more than one row at a time? For example, if I had my table set up like this:

Timesheet Screenshot 1.png (34.43 KiB) Viewed 868 times

And highlighted the right column, the total of hours times rate:

Timesheet Screenshot 2.png (34.34 KiB) Viewed 868 times

Is there any way to set up the formula in Word - i.e. product(left) - so it gives me the totals for all 3 rows at once? Or do I have to do one at a time?

If I could do them all at once, that would be awesomely efficient.

[FanaticGuru]

Do you know if you can do math on more than one row at a time?

I would do something like this:

image.png (10.7 KiB) Viewed 805 times

With a summary at the top of the table. Basically just doing Product(Left) and Sum(Below).

But what I would really do is use Excel, as it is much better for things like this. You could even embed the Excel file into the Word document if you really need it in Word for some reason. Then any changes you make in the Excel sheet will change the table in the Word document. In Excel, the capabilities are almost limitless.

Then in Excel, you could add on to it with AHK so that you could do stuff real-time like hit a button when you start a task and then hit it again when you are complete, like a stopwatch. Then AHK would autofill in the date and 'hours'. Then you just enter the description and rate. There could be a pause feature, etc. There are lots of possibilities.

FG

[AlFlo]

FG,

Creating an Excel spreadsheet with AHK timer functionality sounds great. I did spend countless hours creating an AHK script with multiple stopwatch functions, but it was buggy and inconsistent.

I'd be grateful for a simpler, more consistent script which would interact with Excel in the way you described.

By the way, here's my old stopwatch script:

Code: Select all

#NoEnv
#singleinstance force
Menu, Tray, Icon, C:\windows\system32\shell32.dll,263 ;Set custom Script icon
Settimer, Stopwatch, off

timerm := "00"
timers := "00"
stopped := "0"
timerm1 := "00"
timers1 := "00"
stopped1 := "0"
timerm2 := "00"
timers2 := "00"
stopped2 := "0"
timerm3 := "00"
timers3 := "00"
stopped3 := "0"
timerm4 := "00"
timers4 := "00"
stopped4 := "0"
timerm5 := "00"
timers5 := "00"
stopped5 := "0"
timerm6 := "00"
timers6 := "00"
stopped6 := "0"
timerm7 := "00"
timers7 := "00"
stopped7 := "0"
timerm8 := "00"
timers8 := "00"
stopped8 := "0"
timerm9 := "00"
timers9 := "00"
stopped9 := "0"
timerm10 := "00"
timers10 := "00"
stopped10 := "0"

Gui, Add, Text, x85 y5 w160 h20 vStartTime, Start = %StartTime%
Gui, Add, Text, x40 y52 w160 h20 vStopTime, Stop = %StopTime%
Gui, Add, Text, x155 y30 w60 h20 vTText, %timerm%:%timers%
Gui, Add, Text, x155 y70 w60 h20 vTText1, %timerm1%:%timers1%
Gui, Add, Text, x155 y110 w60 h20 vTText2, %timerm2%:%timers2%
Gui, Add, Text, x155 y150 w60 h20 vTText3, %timerm3%:%timers3%
Gui, Add, Text, x155 y190 w60 h20 vTText4, %timerm4%:%timers4%
Gui, Add, Text, x155 y230 w60 h20 vTText5, %timerm5%:%timers5%

Gui, Add, Text, x155 y270 w60 h20 vTText6, %timerm6%:%timers6%
Gui, Add, Text, x155 y310 w60 h20 vTText7, %timerm7%:%timers7%
Gui, Add, Text, x155 y350 w60 h20 vTText8, %timerm8%:%timers8%
Gui, Add, Text, x155 y390 w60 h20 vTText9, %timerm9%:%timers9%
Gui, Add, Text, x155 y430 w60 h20 vTText10, %timerm10%:%timers10%

;Gui, Add, Button, x2 y42 w80 h20 vPause, Pause
;Gui, Add, Button, x82 y42 w80 h20 vReset, Reset

Gui, Add, Button, x22 y20 w90 h30 gThe_Main_Gui_StopStart1 , &1
gui, add, edit, x220 y20 w760 h30 vMyEdit1	
Gui, Add, Button, x22 y60 w90 h30 gThe_Main_Gui_StopStart2 , &2
gui, add, edit, x220 y60 w760 h30 vMyEdit2
Gui, Add, Button, x22 y100 w90 h30 gThe_Main_Gui_StopStart3 , &3
gui, add, edit, x220 y100 w760 h30 vMyEdit3
Gui, Add, Button, x22 y140 w90 h30 gThe_Main_Gui_StopStart4 , &4
gui, add, edit, x220 y140 w760 h30 vMyEdit4
Gui, Add, Button, x22 y180 w90 h30 gThe_Main_Gui_StopStart5 , &5
gui, add, edit, x220 y180 w760 h30 vMyEdit5
Gui, Add, Button, x22 y220 w90 h30 gThe_Main_Gui_StopStart6 , &6
gui, add, edit, x220 y220 w760 h30 vMyEdit6
Gui, Add, Button, x22 y260 w90 h30 gThe_Main_Gui_StopStart7 , &7
gui, add, edit, x220 y260 w760 h30 vMyEdit7
Gui, Add, Button, x22 y300 w90 h30 gThe_Main_Gui_StopStart8 , &8
gui, add, edit, x220 y300 w760 h30 vMyEdit8
Gui, Add, Button, x22 y340 w90 h30 gThe_Main_Gui_StopStart9 , &9
gui, add, edit, x220 y340 w760 h30 vMyEdit9
Gui, Add, Button, x22 y380 w90 h30 gThe_Main_Gui_StopStart10 , &10
gui, add, edit, x220 y380 w760 h30 vMyEdit10


Gui, Add, Button, x22 y480 w90 h30  gDataSave, &Save Data

Gui, Add, Button, x130 y480 w90 h30 gScratchy, Show Scratch

Gui, Add, Button, x238 y480 w90 h30 gClearScratch, Clear Scratch




; Gui, Add, Button, x248 y500 w90 h30 gThe_Main_Gui_Close, &Quit and Save



gui, add, edit, x10 y600 w980 h90 vMyEdit


Gui, Show, h700 w1000, Stopwatch
Return


Stopwatch:
timers += 1
if(timers > 59)
{
   timerm += 1
   timers := "0"
   GuiControl, , TText ,  %timerm%:%timers%
}
if(timers < 10)
{
   GuiControl, , TText ,  %timerm%:0%timers%
}
else
{
   GuiControl, , TText ,  %timerm%:%timers%
}
return

Stopwatch1:
timers1 += 1
if(timers1 > 59)
{
   timerm1 += 1
   timers1 := "0"
   GuiControl, , TText1 ,  %timerm1%:%timers1%
}
if(timers < 10)
{
   GuiControl, , TText1 ,  %timerm1%:0%timers1%
}
else
{
   GuiControl, , TText1 ,  %timerm1%:%timers1%
}
return

Stopwatch2:
timers2 += 1
if(timers2 > 59)
{
   timerm2 += 1
   timers2 := "0"
   GuiControl, , TText2 ,  %timerm2%:%timers2%
}
if(timers < 10)
{
   GuiControl, , TText2 ,  %timerm%:0%timers2%
}
else
{
   GuiControl, , TText2 ,  %timerm%:%timers2%
}
return

Stopwatch3:
timers3 += 1
if(timers3 > 59)
{
   timerm3 += 1
   timers3 := "0"
   GuiControl, , TText3 ,  %timerm3%:%timers3%
}
if(timers < 10)
{
   GuiControl, , TText3 ,  %timerm3%:0%timers3%
}
else
{
   GuiControl, , TText3 ,  %timerm3%:%timers3%
}
return

Stopwatch4:
timers4 += 1
if(timers4 > 59)
{
   timerm4 += 1
   timers4 := "0"
   GuiControl, , TText4 ,  %timerm4%:%timers4%
}
if(timers < 10)
{
   GuiControl, , TText4 ,  %timerm4%:0%timers4%
}
else
{
   GuiControl, , TText4 ,  %timerm4%:%timers4%
}
return

Stopwatch5:
timers5 += 1
if(timers5 > 59)
{
   timerm5 += 1
   timers5 := "0"
   GuiControl, , TText5 ,  %timerm5%:%timers5%
}
if(timers < 10)
{
   GuiControl, , TText5 ,  %timerm5%:0%timers5%
}
else
{
   GuiControl, , TText5 ,  %timerm5%:%timers5%
}
return

Stopwatch6:
timers6 += 1
if(timers6 > 59)
{
   timerm6 += 1
   timers6 := "0"
   GuiControl, , TText6 ,  %timerm6%:%timers6%
}
if(timers < 10)
{
   GuiControl, , TText6 ,  %timerm6%:0%timers6%
}
else
{
   GuiControl, , TText6 ,  %timerm7%:%timers6%
}
return

Stopwatch7:
timers7 += 1
if(timers7 > 59)
{
   timerm7 += 1
   timers7 := "0"
   GuiControl, , TText7 ,  %timerm7%:%timers7%
}
if(timers < 10)
{
   GuiControl, , TText7 ,  %timerm7%:0%timers7%
}
else
{
   GuiControl, , TText7 ,  %timerm7%:%timers7%
}
return

Stopwatch8:
timers8 += 1
if(timers8 > 59)
{
   timerm8 += 1
   timers8 := "0"
   GuiControl, , TText8 ,  %timerm8%:%timers8%
}
if(timers < 10)
{
   GuiControl, , TText8 ,  %timerm8%:0%timers8%
}
else
{
   GuiControl, , TText8 ,  %timerm8%:%timers8%
}
return

Stopwatch9:
timers9 += 1
if(timers9 > 59)
{
   timerm9 += 1
   timers9 := "0"
   GuiControl, , TText9 ,  %timerm9%:%timers9%
}
if(timers < 10)
{
   GuiControl, , TText09 , %timerm9%:0%timers9%
}
else
{
   GuiControl, , TText9 ,  %timerm9%:%timers9%
}
return

Stopwatch10:
timers10 += 1
if(timers10 > 59)
{
   timerm10 += 1
   timers10 := "0"
   GuiControl, , TText10 ,  %timerm10%:%timers10%
}
if(timers < 10)
{
   GuiControl, , TText10 ,  %timerm10%:0%timers10%
}
else
{
   GuiControl, , TText10 ,  %timerm10%:%timers10%
}
return

The_Main_Gui_StopStart1:
if(stopped = 0)
{
   Settimer, Stopwatch, off
   stopped = 1
}
else
{
   Settimer, Stopwatch, 999
   stopped = 0
}
return

The_Main_Gui_StopStart2:
if(stopped = 0)
{
   Settimer, Stopwatch1, off
   stopped = 1
}
else
{
   Settimer, Stopwatch1, 999
   stopped = 0
}
return

The_Main_Gui_StopStart3:
if(stopped = 0)
{
   Settimer, Stopwatch2, off
   stopped = 1
}
else
{
   Settimer, Stopwatch2, 999
   stopped = 0
}
return

The_Main_Gui_StopStart4:
if(stopped = 0)
{
   Settimer, Stopwatch3, off
   stopped = 1
}
else
{
   Settimer, Stopwatch3, 999
   stopped = 0
}
return

The_Main_Gui_StopStart5:
if(stopped = 0)
{
   Settimer, Stopwatch4, off
   stopped = 1
}
else
{
   Settimer, Stopwatch4, 999
   stopped = 0
}
return

The_Main_Gui_StopStart6:
if(stopped = 0)
{
   Settimer, Stopwatch5, off
   stopped = 1
}
else
{
   Settimer, Stopwatch5, 999
   stopped = 0
}
return

The_Main_Gui_StopStart7:
if(stopped = 0)
{
   Settimer, Stopwatch6, off
   stopped = 1
}
else
{
   Settimer, Stopwatch6, 999
   stopped = 0
}
return

The_Main_Gui_StopStart8:
if(stopped = 0)
{
   Settimer, Stopwatch7, off
   stopped = 1
}
else
{
   Settimer, Stopwatch7, 999
   stopped = 0
}
return

The_Main_Gui_StopStart9:
if(stopped = 0)
{
   Settimer, Stopwatch8, off
   stopped = 1
}
else
{
   Settimer, Stopwatch8, 999
   stopped = 0
}
return

The_Main_Gui_StopStart10:
if(stopped = 0)
{
   Settimer, Stopwatch9, off
   stopped = 1
}
else
{
   Settimer, Stopwatch9, 999
   stopped = 0
}
return

;@  

DataSave:

Gui, Submit, NoHide

Send ^!{PrintScreen}  ; Same as writing Send {Ctrl}{Alt}{PrintScreen}

Sleep, 3500

BillableTime1:=timerm1 / 60
BillableTime2:=timerm2 / 60
BillableTime3:=timerm3 / 60
BillableTime4:=timerm4 / 60
BillableTime5:=timerm5 / 60
BillableTime6:=timerm6 / 60
BillableTime7:=timerm7 / 60
BillableTime8:=timerm8 / 60
BillableTime9:=timerm9 / 60
BillableTime10:=timerm10 / 60

fileappend, %A_MM% %A_DD% %A_YYYY% %A_Hour%:%A_Min% `r`n %MyEdit1%  %timerm1%:%timers1% equals %BillableTime1% `r`n  %MyEdit2% %timerm2%:%timers2%  %BillableTime2% `r`n %MyEdit3%  %timerm3%:%timers3% %BillableTime3% `r`n %MyEdit4%  %timerm4%:%timers4% %BillableTime4% `r`n %MyEdit5%  %timerm5%:%timers5% %BillableTime5% `r`n %MyEdit6%  %timerm6%:%timers6% %BillableTime6% `r`n %MyEdit7%  %timerm7%:%timers7% %BillableTime7% `r`n %MyEdit8%  %timerm8%:%timers8% %BillableTime8% `r`n %MyEdit9%  %timerm9%:%timers9% %BillableTime9% `r`n %MyEdit10%  %timerm10%:%timers10% %BillableTime10% `r`n %MyEdit%, C:\Users\MyName\OneDrive\Desktop\Scratchpad.txt
 
Sleep, 1000

Return

Scratchy:

Gui, Submit, NoHide

Run C:\Users\MyName\OneDrive\Desktop\Scratchpad.txt

Return
 
ClearScratch:

FileDelete, C:\Users\MyName\OneDrive\Desktop\Scratchpad.txt

IfNotExist, C:\Users\MyName\OneDrive\Desktop\Scratchpad.txt, FileAppend,, C:\Users\MyName\OneDrive\Desktop\Scratchpad.txt 

return
;@
;The_Main_Gui_Close:
; GUI, Submit
; Sleep, 1000

; fileappend, %A_MM% %A_DD% %A_YYYY% %A_Hour%:%A_Min% `r`n %MyEdit1%  %A_Space% %timerm1%:%timers1% `r`n  %MyEdit2% %A_Space% %timerm2%:%timers2% `r`n %MyEdit3%  %A_Space% %timerm3%:%timers3% `r`n %MyEdit4%  %A_Space% %timerm4%:%timers4% `r`n %MyEdit5%  %A_Space% %timerm5%:%timers5% `r`n %MyEdit6%  %A_Space% %timerm6%:%timers6% `r`n %MyEdit7%  %A_Space% %timerm7%:%timers7% `r`n %MyEdit8%  %A_Space% %timerm8%:%timers8% `r`n %MyEdit9%  %A_Space% %timerm9%:%timers9% `r`n %MyEdit10%  %A_Space% %timerm10%:%timers10% `r`n, C:\Users\MyName\OneDrive\Desktop\Scratchpad.txt
 
;Sleep, 1000

;Return

;Exitapp


ButtonPause:
if(stopped = 0)
{
   Settimer, Stopwatch, off
   stopped = 1
}
else
{
   Settimer, Stopwatch, 999
   stopped = 0
}
return

ButtonReset:
timerm := "00"
timers := "00"
GuiControl, , TText ,  %timerm%:%timers%
return

GuiClose:
GuiEscape:
ExitApp
return

[FanaticGuru]

Creating an Excel spreadsheet with AHK timer functionality sounds great. I did spend countless hours creating an AHK script with multiple stopwatch functions, but it was buggy and inconsistent.

Here is a simple stopwatch type Gui.

Code: Select all

Start := A_TickCount, Stop := A_TickCount

; Double Buffer Style WS_EX_COMPOSITED = True & WS_EX_LAYERED = true
Gui +LastFound +E0x02000000 +E0x00080000 -Caption +AlwaysOnTop
Gui, Color, Red
Gui, Font, s12 cBlack
Gui, Add, Text, vHeader gMove w225, F12 Start/Stop  |  F11 Reset
Gui, Font, s36 cBlack
Gui, Add, Text, vDisplay gMove xp+20 yp+25 w225, 0:00:00
Gui, Show, w225 h100

Loop
{
	if Running
		GuiControl,,Display, % FormatSeconds(Round((A_TickCount - Start)/1000,0))
	Sleep 200 ; not used for timing just how often the display is updated
}

F12::
	if !(Running := !Running)
	{
		Stop := A_TickCount
		Gui, Color, Red
		Gui, Font, s12 cBlack
		GuiControl,Font,Header
		Gui, Font, s36 cBlack
		GuiControl,Font,Display
	}
	else
	{
		Start := Start + (A_TickCount - Stop)
		Gui, Color, Green
		Gui, Font, s12 cWhite
		GuiControl,Font,Header
		Gui, Font, s36 cWhite
		GuiControl,Font,Display
	}
return

F11::
	Running := false, Start := A_TickCount, Stop := A_TickCount
	Gui, Color, Red
	Gui, Font, s12 cBlack
	GuiControl,Font,Header
	Gui, Font, s36 cBlack
	GuiControl,Font,Display
	GuiControl,,Display, 0:00:00
return

Move:
	PostMessage 0xA1,2
Return

GuiClose:
GuiEscape:
	ExitApp
return

FormatSeconds(NumberOfSeconds)  ; Convert the specified number of seconds to hh:mm:ss format up to 24 hours.
{
    time := 19990101  ; *Midnight* of an arbitrary date.
    time += NumberOfSeconds, seconds
    FormatTime, mmss, %time%, mm:ss
    return NumberOfSeconds//3600 ":" mmss
}

Just uses F11 and F12. Can also be dragged with left mouse.

It could be expanded pretty easily to allow for multiple times at the same time. Would just need to use an array to store the three main variables Start, Stop, and Running. Could spawn a new Gui for each or have them all in one.

I don't really have a use for it but not bad for the length of code. Makes me want to find a use for it.

Does not interact with Excel but it is a start.

Add a F10 for an Excel example:

Code: Select all

F10::
	ComObjActive("Excel.Application").Range("A1000").end(xlup:=-4162).offset(1,0).value := FormatSeconds(Round((A_TickCount - Start)/1000,0))
return

An Excel sheet needs to be open. It should add the current time going down in column A.

FG

[AlFlo]

Thanks, FG! I'll play around with it.

[AlFlo]

FG,

2 questions:

(1) If I want the time to save time in Excel's L column as opposed to the A column, would I change the code from Range("A1000") to Range("L1000") like this:

Code: Select all

ComObjActive("Excel.Application").Range("L1000").end(xlup:=-4162).offset(1,0).value := FormatSeconds(Round((A_TickCount - Start)/1000,0))

?

(2) Can I round up to tenths of an hour, i.e. 6 minute increments, when it saves into Excel?

Thanks!!