[Solved] Track hours in rolling 28-day interval

[JoshuaCJCohen]

Hello,
I am trying to develop a tool that will track hours (hh:mm) in a rolling 28 day time frame. I need to be able to make the "end date" anytime from now to a week in the future. I'm imagining a cell that the "end date" will be input with a button to calculate the answer.

Some days will have an input of zero (or be left blank).

I know I need [hh]:mm to get a total above 24 hours.

I have no idea how to create a rolling 28 SUM function, target date, or activation button.

Thank you,
J

[JoshuaCJCohen]

I found this video that explains what I want to do in LibreOffice but the formula doesn't work in OpenOffice. Can anyone tell me what the changes should be?

https://www.youtube.com/watch?v=isOstdhyuHY

[FJCC]

Does the attached file do what you want? You put an end date in D2, the beginning of the 28 day window is calculated in E2, and F2 shows the sum of column B within that window.

[JoshuaCJCohen]

Does the attached file do what you want? You put an end date in D2, the beginning of the 28 day window is calculated in E2, and F2 shows the sum of column B within that window.

Yes. Thank you! I made some minor changes to the top portion.

Its actually a 672 hour rolling total, not a 28 day rolling total. Minute by minute the back end value drops off.
I've been keeping track of start and stop times manually, i.e. Aug 16th, Start 18:54, Aug 17th, Stop 01:12; Aug 17th, Start 17:15, Stop 23:13.

I was trying to replicate what you did but instead of tracking day by day track minute by minute. Problem is sometimes there are multiple events a day (will this mess up the day counter {F2-27}? I experimented with Aug 30th and it did not seem to cause a conflict) and sometimes the events cross midnight (which complicates the Values calculation. I think I found an easy solution by inputting any value after midnight as X+24, see Testing Sheet D32 as an example.)

I increased the covered area from 101 to 10001 and froze the panel so I don't have to scroll up and down to see results as I enter new data.

Is there an easy way to make this track minute by minute? I created a duplicate sheet (Testing Sheet) and made some changes that I think will make it more accurate. I believe by changing the formula F2-27 to F2-"672:00" I got it to track minute by minute. Do you agree?

Thanks,
J

100 Hour 672 Conflict.ods

(14.14 KiB) Downloaded 49 times

[FJCC]

If you want to go back 672 hours (28 days) from a date & time value, the values you are comparing against should also contain date and time values.

In your example, you have 8/31/2024 16:00 for the End Date in F2. You can get the desired Start Date in G2 with the formula =F2 - 28. That works because dates and times are measured in units of days. Your End Date has the value of about 45535.67. That's the number of days since 12/31/1899. August 31, 2024 is day 45535 and 16:00 is 0.67 of a day. Subtracting 28 from that yields 45507.67, which is August 3 at 16:00.

If you want to look back 672 hours in your Departure column, store those values as full dates and times. For example, instead of writing 15:29 in B67, write 8/31/2024 15:29. Then you can compare your Start and End times against column B and multiple events in one day will not be a problem.

[JoshuaCJCohen]

I think you're saying under the date format is a numerical value of 1 per day since 12/31/1899. So the date I have in F2 isn't really 8/31/24, its a number. In this case it is @ 45535.67.

So if I want the formula to pay attention to the times in B and C I have to include the "date" number and simply having the date in A doesn't cut it. The date in A simply gives the formula in H a range to look at. I'm sure this isn't the proper way to describe what is happening but I'm giving it my best shot.

I'm not sure if this matters but I'm using UTC for all times.

Before I go and add dates to Departure (and Arrivals?) and delete Dates (I think) let me ask a few questions.
1) Do I still need Dates column to provide the date range to H2 or will the Departure Column take care of that? (I think I don't because Departure will have the time info F2 is providing and Dates does not in its current format.)

2) Do I need to put the date in the Arrival column as well?
(I think not because I only care about when the Value started, not when it ended since it drops out of the calculation to the left, not the right.)

3) Depending on those answers which columns need to be in the formula?
(I think it's Departure and Values.)

4) Can I replace "A3:A10004" and "D3:D10004" with "Dates" and "Values"? Likewise with "Departure" and "Arrival" depending on your answer above.

I'm sure you understand what I was thinking but in case you don't I was leaving Dates to provide the date range to H2 and then was using Departure and Arrival to produce the Values data instead of putting it in directly.

Regarding F2 and G2, leave F2 as is and change G2 to "F2-28"? Is "F2-"672:00" wrong or just written differently? If 672 hours is 28 days they are the same, yes? I would assume 672 hours is also worth 45535 but I'm not sure if days are treated differently than hours.

I understand "F2-28" is less cumbersome than F2-"672:00". I just want to understand if your suggestion is a function issue or a style issue.

Thank you

[FJCC]

1) You don't need the Dates column.
2) You will need the date included in the Arrival because Values is calculated as the difference Arrival - Departure.
3) I think you only need Departure and Values.
4) I think you should be able to use the column labels in the formula, but it didn't work for me in a quick test. Maybe because there are so many blank cells? There is an option to turn on column and row labels under Tools -> Options -> OpenOffice Calc -> Calculate. I never use that feature, so I'd have to experiment to find out how it really works.

I'm surprised the formula =F2-"672:00" worked at all. Since dates and times are in units of days, I always use numbers like 7 to subtract 7 days or 3/24 to denote three hours. I guess Calc magically changes "672:00" into 28, but I don't know the limits of that transformation.

I don't understand what you mean by "672 hours is also worth 45535".

[JoshuaCJCohen]

Thank you again.

I'll work on this when I have some time and will post the results.

"I don't understand what you mean by "672 hours is also worth 45535"."

I misspoke. What I mean to say is 28 days is also 672:00 hours. So F2-28 should be equal to F2-"672:00". I was wondering if you were recommending F2-28 over F2-"672:00" for function or style. The former is cleaner. It appears you were recommending the former because you didn't think F2-"672:00" would work but it magically does. I'll swap it back to F2-28 to tidy it up.

[MrProgrammer]

Read sections 3. Dates in cells and 4. Times in cells in Ten concepts that every Calc user should know.

As explained in the tutorial both dates and times are numbers. A number in a cell is just that, a number. The number may have a particular meaning to the person creating the spreadsheet. For example it may represent a date. When you type 2024-08-31 in a cell, Calc interprets it as a date and sets the cell format so it's displayed that way. You can set the cell format to MMM DD YYYY and the cell will show Aug 31 2024. Or you can set the format to NNN and the cell will show Saturday. But the value of that cell is always the number 45535 until you enter a different value in the cell.

If you have a different cell which represents a dollar amount and you enter $45,535.00, Calc will interpret it as currency and display it that way. The dollar sign, the comma, and the period aren't part of the cell's value. They are just formatting characters. The cell value is purely 45535. And note that this is the same cell value as your date cell. Calc allows you to interpret numbers in different ways.

You can perform meaningless operations on cells. You can tell Calc to take the square root of your date. Or you can calculate the cosine of your currency cell. Calc doesn't care. It's up to you to perform meaningful operations on your data.

So F2-28 should be equal to F2-"672:00".

Yes. Calc accepts either expression. Neither is more correct. Use the one which is more meaningful to you. Personally I would write the latter expression as F2-"672:00:00", showing hours, minutes, and seconds, since it may not be clear that "672:00" means 672 hours 0 minutes and not 672 minutes 0 seconds, though I've worked with Calc a long time and know that it means the former. You could use F2-"24:00:00"*28 since it may not be obvious that 672 hours is 28 days. But for me F2-28 is easier to understand.

4) Can I replace "A3:A10004" and "D3:D10004" with "Dates" and "Values"?

Yes. Use Insert → Names → Define and press the Help button. Read about defined names in the User Guides (PDF) or search for topics about that in the Calc Forum. You can use the Name Box to select large ranges of cells. I decline to discuss Defined Names further in this topic since that's not its subject.

4) I think you should be able to use the column labels in the formula, but it didn't work for me in a quick test.

Column labels are a broken idea from Excel. I've seen topics on the forum with strange spreadsheet behavior which disappears when column labels are turned off. Defined names are reliable and offer possibilities that column labels can't achieve.

[JoshuaCJCohen]

Thank you both for your help and patience. Attached is the final product and I think it works 4.0. Please take a look and see if there is anything you don't like or think could be done better.

Cheers,
JCJC

[MrProgrammer]

Please take a look and see if there is anything you … think could be done better.

Use styles instead of direct formatting (Format → Cells).

If it were my spreadsheet I would mark cells which do not contain numbers or formulas with a text style (Numbers → Category → Text). For example : Row 1, Column A, Column E, and K2:R2. When you mark the last range as text you can enter +1 in the cell without having the plus sign removed. I presume you are not going to use the values in calculations since O2 and P2 contain UTC instead of 0. I don't know what ROT is but currently if you enter 0123 in E5 the leading zero is removed.

D29:D34 contain numbers instead of a formula, an error? Column E contains a mixture of text and numbers, an error? Check that using View → Value Highlighting. I avoid mixing text and numbers in a column. All of the values would be text if you'd marked column E that way before entering data. You cannot convert numeric values to text values by changing the cell format. Convert values using [Tutorial] Text to Columns or, for a few cells, just re-enter the values after changing the formatting.

For cell I2, you may want to consider how you want it displayed if H2 exceeds 100 hours.

Clear cell C5 and observe what happens to D5. To avoid that in D5 use formula =IF(COUNT(B5:C5)=2;C5-B5;"").

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Alex1]

You used the wrong character, ` instead of ', to force text in cells K2:R2. I could not find what ALT in A64 means.

[JoshuaCJCohen]

Use styles instead of direct formatting (Format → Cells).

I will take a look at this, thank you for the suggestion.

If it were my spreadsheet I would mark cells which do not contain numbers or formulas with a text style (Numbers → Category → Text). For example : Row 1, Column A, Column E, and K2:R2. When you mark the last range as text you can enter +1 in the cell without having the plus sign removed. I presume you are not going to use the values in calculations since O2 and P2 contain UTC instead of 0. I don't know what ROT is but currently if you enter 0123 in E5 the leading zero is removed.

Understood and implemented. K2:R2 are non-calculations, just time zones. Column E is titles that use both letters and numbers, no error here.

D29:D34 contain numbers instead of a formula, an error?

Yes, fixed.

Column E contains a mixture of text and numbers, an error? Check that using View → Value Highlighting. I avoid mixing text and numbers in a column. All of the values would be text if you'd marked column E that way before entering data. You cannot convert numeric values to text values by changing the cell format. Convert values using [Tutorial] Text to Columns or, for a few cells, just re-enter the values after changing the formatting.

Understood. I think I corrected it.

For cell I2, you may want to consider how you want it displayed if H2 exceeds 100 hours.

Currently it goes over 100 and I2 goes negative a corresponding amount. Do you mean the cell should turn red or something to catch my attention?

Clear cell C5 and observe what happens to D5. To avoid that in D5 use formula =IF(COUNT(B5:C5)=2;C5-B5;"").

Thank you for the suggestion but I like the current effect. The huge negative number immediately alerts me that something is wrong.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

Will do! Thank you again!

[JoshuaCJCohen]

You used the wrong character, ` instead of ', to force text in cells K2:R2. I could not find what ALT in A64 means.

Got it. ALT is supposed to be ATL. My bad.

[JoshuaCJCohen]

Use styles instead of direct formatting (Format → Cells).

I will take a look at this, thank you for the suggestion.

If it were my spreadsheet I would mark cells which do not contain numbers or formulas with a text style (Numbers → Category → Text). For example : Row 1, Column A, Column E, and K2:R2. When you mark the last range as text you can enter +1 in the cell without having the plus sign removed. I presume you are not going to use the values in calculations since O2 and P2 contain UTC instead of 0. I don't know what ROT is but currently if you enter 0123 in E5 the leading zero is removed.

Understood and implemented. K2:R2 are non-calculations, just time zones. Column E is titles that use both letters and numbers, no error here.

D29:D34 contain numbers instead of a formula, an error?

Yes, fixed.

Column E contains a mixture of text and numbers, an error? Check that using View → Value Highlighting. I avoid mixing text and numbers in a column. All of the values would be text if you'd marked column E that way before entering data. You cannot convert numeric values to text values by changing the cell format. Convert values using [Tutorial] Text to Columns or, for a few cells, just re-enter the values after changing the formatting.

Understood. I think I corrected it.

For cell I2, you may want to consider how you want it displayed if H2 exceeds 100 hours.

Currently it goes over 100 and I2 goes negative a corresponding amount. Do you mean the cell should turn red or something to catch my attention?

Clear cell C5 and observe what happens to D5. To avoid that in D5 use formula =IF(COUNT(B5:C5)=2;C5-B5;"").

Thank you for the suggestion but I like the current effect. The huge negative number immediately alerts me that something is wrong.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

Will do! Thank you again!

You know, I tried doing that and when I went into the preview it didn't work so I just deleted all the "[quote......Ah. I see. "/" would have been helpful.