I have 4:00:00 AM in the B2 and I tried to add +(4*60) ie 4 hours to it and the answer is 12:00:00 AM should be 8:00:00 AM.
i.e. 24 days. Time is a fraction of a day so cell C3 in your screenshot should read =B3+(4*1/24)
The fact that you have 12:00:00 AM displaying instead of 04:00:00 AM indicates that there is some other issue in the sheet, possibly a number or formatting issue. Can you upload a sample using the 7th icon from the left with tooltip Upload
1 Like
- Such Images mostly don’t tell anything useful. Attach a little example file (
.ods)
in such a case. - Abandon outdated time (and date-time) formatting. Use
ISO 8601.
4*60 formatted as a date-time means 240 days, not 240 minutes. The day is the mandatorily used unit for the calculation of such values. - 4 hours is therefore represented by the result of
= 4/24(=1/6).
B2 is empty. So the result in C3 is correct, since 12:00:00 AM is 00:00:00.
Once you understand the *1/24, the formula can be simplified to =B3+4/24.
1 Like
I missed that, some bizarre blank row…
I am aware of that, however given the misunderstanding of time, it is best not to change too much. It is possible that the 4 hours are entered in another cell so the formula could end up as =B3+J3/24 if J3 contains the 4 for example
1 Like
Yeah, so that brings up another issue. It was in B2 and I added a row to sepete the data from the header. Never thought to look at the reference. I’ve never noted that happening before.
The question I have is why they deveated from Excel’s time process which I think is horrendous by-the-way? I did read the article regarding the 1/24th but I just wrote it off as being for some other situation. It’s not most compete documentation especially in comparison to Excel’s date and time calculations. They didn’t even have a blurb on adding time-of-day to a period-of-time.
I was a long-time Excel user and I had purchased an MS Office 2021 license back some years ago. They just stopped my ability to use the license trying to force me onto Office 365, rent for life, but there are things that don’t follow the Excel standard if you want to call it that. Google Sheets are even worse and that’s kind of how I landed here. Is there a comparison between the 2 products that lists the exceptions?
You’re right about the screen capture because of the formating it could be very misleading.
See Inaccurate calculation of Time-of-Day plus Time Span - #7 by CityguyUSA
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.
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
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?
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
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.
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.
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.
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)
I don’t have access to Excel anymore.
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.
So apply the formatting that you prefer, for column A apply DDDD, for column B apply HH:MM:SS AM/PM
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.