1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

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

    From PaddyStan@21:1/5 to All on Wed May 12 02:56:34 2021
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Auric__@21:1/5 to All on Thu May 13 01:46:14 2021
    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!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Auric__@21:1/5 to PaddyStan on Thu May 13 01:44:29 2021
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)