I’m trying to set a sheet to read two columns. On each row is the start time (C) and end time (D). I’m trying to get a total number of hours out of the sum of those two time formated columns in that row. No matter what I’ve done so far it just keeps coming back as a time even with column formatted to numbers.
I guess your data is not imported as date, but as text. A date is an integer representing the passed days since begin of 1900. Time us a fraction of a day with 24 hours represented as 1, so high noon is 0.5. Both are numbers.
You may find a tiny apostrope at the beginning of your value. And text is left-alinged by default, dates/time/numbers go to the right border.
Formatting can not change the value, it can only change the representation of the cell.
Try text-to-columns like in this solution:
And maybe find out how to avoid this situation by selecting the right type during import. The type of the columns can actually be changed.
The data wasn’t imported. It was entered directly.
I was just guessing, as this is the most common reason for date/time as text.
You may upload a shortened sample. Then we may check, where the problem is…
If you didn’t alter alignment: are your cells left- or right-bound?
Just tracking the house of someone working on our home. I did have a lot of trouble trying to format the colums.
| location | date | start | end | Hours |
|---|---|---|---|---|
| Lewiston | 09-02 | 09:30:00 AM | 04:00:00 PM | 7.5 |
| Home | 09-03 | 10:00:00 AM | 01:00:00 PM | 3 |
| home | 09-07 | 0.5 | ||
| home | 09-08 | 11:00:00 AM | 01:30:00 PM | 2.5 |
| home | 09-11 | 08:30:00 AM | 01:00:00 PM | 5.5 |
| home | 09-15 | 08:30:00 AM | 12:30:00 PM | 4 |
| home | 09-16 | 09:30:00 AM | 01:00:00 PM | 4.5 |
| Total Hours | 27.5 | |||
| Cost | 687.5 |
[erAck: edited to make that a table; see there and that generator]
Well that’s not quite what I was trying to cut and paste.
https://docs.google.com/spreadsheets/d/1eq6JcuiIlGOD9G0gqnasaZ4D0eGAq8zQmPyk-lvxakA/edit?usp=sharing
Google Sheets would undergo file format change.
Why don’t you just edit your question (pencil icon) and upload (block with upward pointing arrow) a sample .ods? Cheers, Al
Actually, don’t worry I used your text, I just can’t see what you had before. Have a look at the formatting (in the menu click Format > Cells > Numbers or Ctrl+1), especially the difference between time and times. Cheers, Al
TimeCalcs.ods (12.3 KB)
BTW this is an international site, it is best to use YYYY-MM-DD for dates here; never use just MM-DD in any spreadsheet unless you want unexpected interpretation.
I have that. What I need is a formula to figure out how many hours total between the two columns. How many hours did he work and enter that in a column already set up that will total hours and then total wages. Those parts I have working. Right now I’m having to figure out the hours manually and I keep screwing that up.
Going from the document EarnestAI attached, just sum the values in column E, so in E10 would be the formula =SUM(E2:E8) (that would give 24:00:00) and to get the actual number of hours to calculate with (e.g. to multiply it with an hourly wage) multiply that value with 24 (because internally all date+time values are handled as fractions of days), so =SUM(E2:E8)*24 gives you the amount of total hours.
Btw, the table you provided in the comment has errors in the Hours column, some values are calculated wrongly, in the document you see the correct values calculated.
1 Like
Took me two attempts to get it editable but I see the formula now and it makes sense to my limited brain. Thank you much EarnestAI
=end-start+(end<start)
formatted in any time format you want
- gives the difference as a day fraction formatted to show the hours, minutes, seconds of a day
- if the end time is smaller than the start time, the formula adds one day, so 3:00 - 21:00 is equivalent to 1/8 - 7/8 +1 and yields 1/4 of a day, formatted as time: 6:00
ALL times in all spreadsheets are fractions of days. ALL dates in all spreadsheets are day numbers. When adding or subtracting dates/times, you always add or subtract numbers in unit “days”.
Alternatively, multiplication by 24 gives the plain number of hours. Remove any time formatting.
=24*(end-start+(end<start)) with start=21:00 and end=3:00 gives 24 * 1/4 = 6
1 Like
I’ve got all the daily time working due to all your excellent help. I seem to have it squared away now. Thank you so much for all your help. I don’t use a spreadsheet often enough to be able to figure out how it works.
Ok. Now I am majorly confused and befuddled. For column E that is for total hours I’m using =D row number - C row number. This given me a correct total for some rows but incorrect for others. It’s got me pulling out what little I have left of my hair. This is my current version. Row 3 is wrong. Row 9 is wrong. Row 12 is wrong.
What am I doing wrong?
Posting Google sheets on a LibreOffice site.
Edit your question to upload an ods file or ask on a Google sheets forum. Cheers, Al
I used Libreoffice Calc to make the sheet. I saved it as a ODF. I uploaded that file. Does google convert it when it’s uploaded to google drive?
What the heck. That’s not what I uploaded.
How do I just upload the file so you guys can look at it? This is all getting way beyond me. Might just be easier to do it all by hand.
Edit your question, and use the Upload icon (an upward arrow over a rectangle).