Inaccurate calculation of Time-of-Day plus Time Span

English
#9

See Inaccurate calculation of Time-of-Day plus Time Span - #7 by CityguyUSA

In a lot of these forums you can usually click on the post number to get a link but there doesn’t seem to be a post# in here and I’m not an expert in these forums. I’m just learning to seperate code from the commentary. Sometimes I can do successful uploads but it really depends on the board, browser and OS. The other day I came across a forum that I couldn’t use any of the controls in Brave Android but could in other Android browsers. I had to go to Window’s to upload my image.

#10

Calc operates exactly the same way as Excel for this calculation, both use a fraction of a day for time. Starting dates for year 0 are different however.

Try Getting Started with LibreOffice and the Calc Guide, download from LibreOffice Bookshelf

Why can't I see the post I posted yesterday?
#11

I never did any divide by 24 stuff in Excel. I’m not saying there may not be logic underneath that does that but I never had to put it in a cell formula. In fact, you could add whatever time variant you wanted to day-of-the-week and a time-of-day and Excel would also calculate the correct day if you added enough time to cause it to change days.

How does that work in Calc? Can I just add the day-of-the-week into my time-of-day cell?

#12

If you enter 04:00:00 in a cell then you don’t need to divide, by entering it as a time format you already have entered it as a fraction of a day. If you format that cell as a fraction with format code # ?/24 it shows 4/24. Reformat it as time with format code HH:MM:SS and it will revert to 04:00:00
.
If you add 04:00:00 to 04:00:00 you will get 08:00:00

#13

I tried going back to find a spreadsheet I had done some time ago but I can’t remember the filename anymore. I don’t remember if I entered it formated in any special way or not. I do remember it was finicky especially when using dates and times together and I remember adding durations as not being as simple as I thought they should be always requiring me to recheck their documentation.

This is what comes back as the answer to adding a duration to a start time in Excel:

To calculate when a task finishes based on a start day and time, add the execution duration to the start time/date. In Excel, a full day equals \(1\), so hours are calculated as \(\frac{1}{24}\).Assuming your Start Day (e.g., Monday) is in A2, Start Time (e.g., 8:00 AM) in B2, and Execution Duration (in hours) in C2, use this formula:=A2 + B2 + (C2/24)Format the formula cell as dddd h:mm AM/PM to display the exact completion day and time.

So there’s that /24. I swear that was never a thing. It makes me sound like a liar but I know I was mutipling by 60 for seconds and 60 for minutes, etc. There use to be some crazy number underlying every date it had like 16 numbers or something and formatting was half the battle but it never seemed easy to conceptualize.

Anyway I did exactly as it says from above and the format of dddd h:mm AM/PM but it doesn’t work. Even if it works it’'s combining the day and the time in one cell I need to split them back out so I can use the same calc on the next line otherwise I have an oddity.

Screenshot_20260715_001109_RVNC Viewer
Screenshot_20260715_001109_RVNC Viewer1200×1109 295 KB

#14

I guess you mean the internal representation of dates, being days counted from the beginning of 20th century (and old versions of Excel ignoring 1900 being no leap year, and introducing a second starting base in year 1904).

To test just enter 1 in a cell, then change formatiing to date.

#15

That is your decision how you handle this. But to remember: Formatting does not change/convert values. It just displays them. If you have a day.fraction “timestamp” formatted as a date the time is still there, but hidden.
You can truncate the timestamp to have only a date, but the value is equal to the timestamp at 00:00 AM on the same day.

#16

Is this what you want? Note that I have repeated the date/time 3 times in each row, two cells are formatted differently and one uses WEEKDAY function to extract day or week. Formulas are filled to row 31. Just select the Length of Backup (S, M, L) from the drop down selection and enter the Drive letter.
TimeSpan136386EA.ods (13.3 KB)

#17

I don’t have access to Excel anymore.

#18

What I want as an answer:

Sunday 4:00:00AM in A4 and B4 but I’m not even getting the answer formatting or not let alone having the day in column A and the Time in column B. And then I’ll proliferate it down the column.

#19

So apply the formatting that you prefer, for column A apply DDDD, for column B apply HH:MM:SS AM/PM

#20

This seems to work but it’s very different than what the search result suggested. You’re using and actual date and time and formatting them away. Does this work regardless of the actual underlying date would be my question? I don’t want it to be date dependent but day-of-the-weeek and time-of-day dependent. If that makes sense. Maybe I’ m over thinking.

#21

Yes. A date is implicit for time in real life and in spreadsheets. If you don’t specify a date then Calc will use 0 which corresponds to the start date set in Tools > Options > LibreOffice Calc > Calculate, default is 1904-01-01
.

The sequence of days never alters, only the starting day, e.g. Sunday so it won’t affect your spreadsheet. If I set E5 to L in my sample, you can see the time overruns to Monday which is what you would expect.

#22

So we’re back to convuluted. There’s no way to just have a day of a week and a time and add an add a length of time as suggested by the a3+b3+(f3/24).

To get my next item you’re saying just calculate the formula in a4 and also in b4 or just do an =a4 in b4 and set the format for the A4 to expose the day-of-the-week and b4 to expose the time-of-day.

#23

??? Why not ?? Try something:
A3 is any day, for example 4567
B3 contains 0.25 (would be formatted as 6 AM)
When we assume 6 (hours) in f3
your formula would give 4567+0.25+(6/24) = 4567.5
So you have a result representing 12:00 on tge same day as before.

Not much. You could develop your own set of functions working in the same way as the current set or you can accept thousands of users of excel and calc working with real dates successfully.

If you prefer day 1 to seven you may use them. To “transfer” this dates in a real date just add the last Sunday, if your weeks start on mondays.
(July, 12th +1 would be Monday July, 13th)

Depends. Check, if you are affected by corner cases like

  • “10 days that never existed” at transition from Julian to gregorian calender.
  • Will your calculations be affected by changing to summertime (for example night shifts)
  • Travellers need also to include timezone for some calculations.
#24

I find it weird that you have to reply to some particular post to be able to add to the discussion and yet replying doesn’t automatically include the text of what you’re replying too.

Anyway. I’m bugged by this need to add these 3 cells together. I haven’t let go yet. I’ve changed =a3+b3+(f3/24) it’s now =sum(a3,b3,f3) ignore the actual column number because I’ve added some columns to show what the calculation result should be and then recalculated on the corrected values. That seems to get the time right but the day is just wrong. It seems to get screwed up going past 24 hours trying to calc the correct day-of-the-week.

See what you think.
BackupSchedule.ods (19.7 KB)

#26

@CityguyUSA

It Could Be So Simple.

BackupSchedule99.ods (31,3 KB)

#27

Mark the part you’d like to include before click on the speech-bubble icon.

#28

That sounds familiar.

#29

Open a new comment or solution as appropriate.
Select the text to quote.

quote
quote1527×773 23.2 KB


Click Quote to add the quote, including a reference to the poster and a link to the post.

Why can't I see the post I posted yesterday?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.