Difference in value of a cell in Excel at two different time.

15 views
Skip to first unread message

PaddyStan

unread,
May 12, 2021, 5:56:36 AM5/12/21
to
I need something to record what the value was in a cell in Excel at 00:01 on a Monday and record the value in the same cell at 11:59 the following Sunday and calculate the difference between the two.

Is this possible?

Many thanks

PaddyStan

Auric__

unread,
May 12, 2021, 9:44:31 PM5/12/21
to
PaddyStan wrote:

> I need something to record what the value was in a cell in Excel at 00:01
> on a Monday and record the value in the same cell at 11:59 the following
> Sunday and calculate the difference between the two.
>
> Is this possible?

Write a batch file. Copy this into notepad:

del "Monday 00-01.xlsx"
copy "my workbook.xlsx" "Monday 00-01.xlsx"

Save that to a file that ends with .bat (and not .txt) in the same directory
as your spreadsheet. (Replace "my workbook.xlsx" with the actual name of
your spreadsheet.) Schedule that to run every Monday at 00:01. Then copy
this into notepad:

del "Sunday 11-59.xlsx"
copy "my workbook.xlsx" "Sunday 11-59.xlsx"

Save that to a different file that also ends with .bat. Schedule that to run
every Sunday at 11:59. Then in your report spreadsheet, add this sub:

Sub compare()
Dim monday As Workbook, sunday As Workbook, target As Worksheet
Set target = ActiveSheet
Set monday = Workbooks.Open("D:\Monday 00-01.xlsx", ReadOnly:=True, _
AddToMru:=False)
Set sunday = Workbooks.Open("D:\Sunday 11-59.xlsx", ReadOnly:=True, _
AddToMru:=False)
target.Range("A1").Value = sunday.ActiveSheet.Range("A1").Value - _
monday.ActiveSheet.Range("A1").Value
monday.Close
sunday.Close
Set monday = Nothing
Set sunday = Nothing
End Sub

Run that whenever you want to see the results. (There are better ways to do
this, but this way doesn't require you to save the data anywhere else.)

--
I don't know and I have no opinion.

Auric__

unread,
May 12, 2021, 9:46:16 PM5/12/21
to
I wrote:

> Sub compare()
> Dim monday As Workbook, sunday As Workbook, target As Worksheet
> Set target = ActiveSheet
> Set monday = Workbooks.Open("D:\Monday 00-01.xlsx", ReadOnly:=True, _
> AddToMru:=False)
> Set sunday = Workbooks.Open("D:\Sunday 11-59.xlsx", ReadOnly:=True, _
> AddToMru:=False)
> target.Range("A1").Value = sunday.ActiveSheet.Range("A1").Value - _
> monday.ActiveSheet.Range("A1").Value
> monday.Close
> sunday.Close
> Set monday = Nothing
> Set sunday = Nothing
> End Sub

I forgot to mention, replace "A1" in target with the cell you want the
results placed in, and replace "A1" in sunday and monday with the address of
the source data.

--
You people have the morals of guinea pigs!
Reply all
Reply to author
Forward
0 new messages