Share via

Keep rolling total of two columns being subtracted

Jesse 1 Reputation point
2020-09-22T15:49:06.73+00:00

26555-excel.png

Good Morning,

I need some help on an excel formula that subtract the time(c column) a persons state(H Column) goes from Logon to Avail or logoff and tallys that for the full column. Goal is to have a rolling total count how long a person takes from logonto go into avail or logoff.

Thank you,

Jesse

Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
2,069 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. SethWH 436 Reputation points
    2020-09-22T19:25:22.273+00:00

    If this a CSV file that you normally export, it might be worth it to use PowerShell's CSV functionality to read each row and capture Date+Time as $StartDate for records with Status LOGON. Then you could use a function like this to get the difference in hours in a variable (Ex.): 

    $Count = NEW-TIMESPAN -Start $StartDate -End $EndDate

$Count.totalhours

    This is just and idea of what you could do. You might want to try the PowerShell forum if want to learn more.

  2. Herbert Seidenberg 1,191 Reputation points
    2020-09-23T01:34:31.367+00:00