Share via

Using TIME with formulas and sorting

Anonymous
2021-11-09T11:00:16+00:00

I am doing a time-study and have set up an Excel spreadsheet that should allow me to calculate the amount of time spent throughout the day on every task. In separate columns I have set up Date, Start Time, End Time, Time Elapsed, and various other columns (e.g., to categorize the tasks, add notes, etc.) But I know I set it up wrong and clearly need to be using a specific time format (perhaps military?). As it stands now, I can't sort and resort chronologically because Excel doesn't recognize, for instance, that 1:00pm comes AFTER 12:59pm. Also, I didn't even add the AM/PM initially to the time columns, so they are sitting in their own columns (not smart, I know!). In the Time Elapsed column, a simple formula subtracting End Time from Start time DOES give me what I need most of the time, but not when the interval includes the transition from 12:59 to 1:00. And even for those rows where I THINK it yields the right calculation, trying to sum the Time Elapsed column doesn't work. I found somewhere from Googling that it reads 12:00 as 0.5 (because 12:00 is half of the 24-horu time period).

In any case, I would SO appreciate any guidance re. how to set this up appropriately. Here's a little example from my spreadsheet. Any suggestions re. how to convert this to something that works? Note that the two rows beginning with 12:56am and 1:00 AM were originally ONE row (from 12:56 to 1;10AM), but I had to split it into two so it could calculate the Time Elapsed. Thank you!! Megan

Day Date Begin Time AM/ PM? End Time AM/ PM? Time Elapsed Category
Wednesday 10/27/2021 8:23 PM 8:28 PM 0:05 Cross
Wednesday 10/27/2021 8:28 PM 8:32 PM 0:04 Planning
Wednesday 10/27/2021 8:32 PM 9:00 PM 0:28 Data Admin
Wednesday 10/27/2021 9:00 PM 10:20 PM 1:20 Data Admin
Wednesday 10/27/2021 10:20 PM 11:45 PM 1:25 COVID
wee hours 10/28/2021 12:05 AM 12:15 AM 0:10 Enr-Supv
wee hours 10/28/2021 12:20 AM 12:55 AM 0:35 Mgmt Admin
wee hours 10/28/2021 12:56 AM 12:59 AM 0:03 Planning
wee hours 10/28/2021 1:00 AM 1:10 AM 0:10 Planning
wee hours 10/28/2021 1:10 AM 1:20 AM 0:10 COVID
wee hours 10/28/2021 1:25 AM 1:52 AM 0:27 Ev-Admin
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-11-10T12:36:07+00:00

    You haven't given outsiders access to that file...

    I am in The Netherlands, by the way.

    0 comments
  2. Anonymous
    2021-11-10T10:07:57+00:00

    Yes, I would appreciate that so much! Since my last email, I tried to do some research on TIMEVALUE to understand more. I WAS able to convert my time column to a decimal by simply reformatting it as text (Columns E and I) However, without the AM/PM in the same column as the time (and the concatenation didn't work), the system doesn't always assume accurately whether it IS AM or PM. But I did recognize that I could add .5 to the converted time after 1pm to make it numerically accurate. (For instance see Column G beginning on Row 19.) I'm still experimenting here! I guess I'm hoping to understand (1) what do I need to do in Column M so I can calculate Time Elapsed (Column I minus D), and how to best enter the time going FORWARD, because I will continue this time study. I have a couple of additional questions in blue highlight in Column C. Iti's really late here in LA and I'm falling asleep(!), so I will stop here now. I don't use DropBox. If i send you the link to my OneDrive doc, will that work? Here it is: Time-Tracker - 11.9.21 - testingB.xlsx. I so appreciate your help, Hans! Are you in the Netherlands or Scandinavia maybe? Thank you, Megan

    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-11-10T08:51:35+00:00

    Could you make a copy of the workbook without sensitive information available through OneDrive, Google Drive, Dropbox or similar?

    0 comments
  4. Anonymous
    2021-11-10T06:10:03+00:00

    Hello, and thank you so much. Unfortunately, it did not work. It populated the cell with this: #VALUE! It also gave me that little symbol on the left that asks if you want to convert it to a number, but it also said there was an error. I selected "Ignore Error" but nothing changed. Also, I'm not sure whether I'm supposed to be using a specific NUMBER format, but I'm unable to get it to work! If you happen to have any other ideas to suggest, I would so appreciate it! Either way, thank you so much for trying. Megan

    0 comments
  5. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-11-09T11:32:49+00:00

    Let's say Begin Time is column C and the corresponding AM/PM is column D.

    In another column, say column K, enter the following formula in row 2:

    =TIMEVALUE(C2&" "&D2)

    Format the cell with the formula the way you want, then fill down.

    Similarly, assuming End Time and its AM/PM are columns E and F, use the following in for example L2

    =TIMEVALUE(E2&" "&F2)

    Does this work? If so, use the following for Time Elapsed:

    =MOD(L2-K2,1)

    This will calculate the correct time even if Begin Time is before midnight and End Time is after midnight, for example 11:50 PM and 12:10 AM.

    0 comments