Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 12th 21, 10:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2020
Posts: 2
Default Difference in value of a cell in Excel at two different time.

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

  #2   Report Post  
Old May 13th 21, 02:44 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 538
Default Difference in value of a cell in Excel at two different time.

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.
  #3   Report Post  
Old May 13th 21, 02:46 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 538
Default Difference in value of a cell in Excel at two different time.

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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Excel 2007 bug in difference between time Jon Excel Discussion (Misc queries) 3 December 19th 08 02:10 PM
subtract the time difference from another time difference Dannigirl Excel Discussion (Misc queries) 3 September 30th 07 03:47 PM
Time difference calculations, daylight savings time, Excel Tim Excel Discussion (Misc queries) 1 December 28th 06 04:18 PM
Negative time should be allowed in Excel, eg time difference Bengt-Inge Larsson Excel Discussion (Misc queries) 2 October 13th 05 12:59 PM
Cell formats, and time difference Peter Kirk Excel Discussion (Misc queries) 1 May 25th 05 10:41 PM


All times are GMT +1. The time now is 02:40 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017