Thank you SO much, I just spent hours looking through articles and other u tube video's getting confused. Then I stumbled across your channel which made my day. You're a great teacher, as the tutorial was concise, informative, and right on point. It's five out of five Gold Stars of greatness from me.
Oh, thank you so much! That is so nice of you. Thanks for taking time to leave a nice comment. I really appreciate it! Glad you found my Channel and that the videos are helpful to you. Take care!
Sharon, finally a good tutorial on how to handel time in excel. I need help on a formula to display time when its past midnight. Here is the issue my server tells me the "minutes since event" when I run an event log in the mornings. I downloaded the event log in excel and added the current time. I subtract the "minutes since event" column and format the cell like this =+$D$1-TIME(0,B10,0) Where $D$1 is the time the log was run and B10 is the minutes since the event. All is good until we pass midnight and Excel has to change fro AM to PM, when this happens, my formula returns the # sign. Can you help me? Some of the minutes on the event log are over a 1000, so that's over 12 hours. Thanks, Mike
Very helpful, but not exactly what I need. I am trying to figure out how to calculate the difference between actual hours worked versus scheduled hours. For example if I am expected to work from 8-5, but only work 10-3. Is there a formula that would do the math and show it as a negative. I was able to use your techniques, but the negative shows as ##########.
Thank you! To calculate the difference between actual hours worked and scheduled hours in Excel, you can use a similar technique to what I showed in the video, but with a few tweaks. If your scheduled hours are 8:00 AM to 5:00 PM and actual hours are 10:00 AM to 3:00 PM, you can use this formula: =Actual_End_Time - Actual_Start_Time - (Scheduled_End_Time - Scheduled_Start_Time) However, Excel doesn’t display negative time values by default, resulting in the ########## error. Here’s how to handle this: Use an IF Formula for Text Formatting: =IF(Actual_End_Time - Actual_Start_Time < Scheduled_End_Time - Scheduled_Start_Time, TEXT(Actual_End_Time - Actual_Start_Time - (Scheduled_End_Time - Scheduled_Start_Time),"-h:mm"), Actual_End_Time - Actual_Start_Time - (Scheduled_End_Time - Scheduled_Start_Time)) This displays the negative difference as text while preserving the time calculations. Please let me know if this works for you. Thanks for watching!
Hi Sharon, thank you for the great explanation. I was wondering< if you have per say; a flight arrival time and a hotel check-in time and you want to calculate if you'll arrive early or late for the check-in, how you do that? (when I tried, negative times showed the dreaded hashtags) :)
Hello! Thanks so much for subscribing to my Channel! If you input the date and time into the cells, and ensure the cell is formatted for Date & Time (or a custom format) then you can simply subtract the end time of day 1 from the start time of day 2 and multiply by 24 to get the result in hours of elapsed time. For example: =(B1-A1)*24 I hope this helps! Thanks for watching!
How about that when I have negative hours from last month that are overtaken to the actuall month? Like I have -07:00:00 (-7 hours). I cant just type it into the cell, so the only way to make it, it is a format as number?
Handling negative time values in Excel can indeed be tricky, especially when dealing with hours that span over different months. Excel does not natively support negative time values when using time formats like hh:mm:ss. However, you can work around this limitation using number formats and custom formulas. Here's a step-by-step guide to handle negative time values effectively: Method 1: Using Decimal Numbers for Hours Convert Time to Decimal Hours: Convert your time into decimal hours. For instance, -07:00:00 can be represented as -7. Enter Decimal Hours: Simply enter -7 in the cell. Ensure that the cell is formatted as a number, not as time. Calculate Total Hours: You can add or subtract these decimal hours as needed. For example, if you have -7 hours in one month and you want to add 15 hours from another cell, simply use the formula: =-7 + 15 This will give you 8 hours. Method 2: Using a Custom Formula to Handle Negative Time If you prefer to keep using the hh:mm:ss format, you can create a custom formula to handle negative times. Here's how: Enter Time Values: Enter your times as usual, but for negative times, you'll need to handle them with a formula. For example, let's say you have 07:00:00 (positive 7 hours) in cell A1 and you want to represent -07:00:00. Create Custom Formula for Negative Time: Instead of entering -07:00:00, enter the positive value 07:00:00 in a cell (e.g., A2) and use a formula to convert it to a negative time. In another cell (e.g., A3), use a formula like: =-HOUR(A2) - MINUTE(A2)/60 - SECOND(A2)/3600 This will convert 07:00:00 in A2 to -7. Summing Time Values: To add or subtract these time values, convert them all to decimal hours first using the formula above, then sum them up. Example Workbook: Imagine you have: A1 with the value 7:00:00 (positive 7 hours) A2 with the value 07:00:00 (to be treated as negative 7 hours) In B1, use the formula: =-HOUR(A2) - MINUTE(A2)/60 - SECOND(A2)/3600 This will convert 07:00:00 to -7. In C1, you can add these times: =HOUR(A1) + MINUTE(A1)/60 + SECOND(A1)/3600 + B1 This will give you 0 because 7 - 7 = 0. Displaying as Time Again If you want to convert the result back to a time format: =TEXT(C1/24, "[hh]:mm:ss") This will convert your decimal hour result back into a time format. By using these methods, you can handle negative times and perform calculations across different months without the limitations of Excel's time format. I hope this helps! Thanks for watching!
Here are some troubleshooting tips to try: Adjust Cell Width: Number signs (###) typically appear when the content of a cell is too wide to fit within the column width. Suggest that they try widening the column containing the result to see if the actual value becomes visible. Check Number Formatting: Ensure that the cell containing the formula or the result is formatted as a time value. Right-click on the cell, select "Format Cells," and choose a time format (e.g., "hh:mm:ss" or "h:mm"). Use Proper Time Format: Make sure that the time values being used in the formula are entered correctly. Time values in Excel should be in the format of "hh:mm" or "h:mm AM/PM" for 12-hour format. Check Formula Syntax: Verify that the formula being used to deduct minutes from time is correct. The formula should subtract the desired number of minutes from the time value. For example, to deduct 15 minutes from a time value in cell A1, the formula should be: =A1 - TIME(0, 15, 0). Confirm Cell References: Double-check that the cell references in the formula are accurate. Ensure that the cell reference for the time value and the deduction value are correct. Check for Errors: If the above steps don't resolve the issue, ask the person to check if any error messages are displayed in the cell. Common errors include referencing empty cells, using incorrect functions, or having circular references. Test in Excel Desktop: If the person is using Excel Online, suggest that they try the same deduction formula in the desktop version of Excel (if available) to determine if it's a limitation of Excel Online or a specific issue with their formula. Hope this helps! Thanks for watching my videos!
Can Excel calculate the duration of time over many days? For example, i start my well pump on June 1 at 10 AM and turn it off on June 7 at 3PM. Is there a formula that gives me the amount of hours and minutes the pump was on?
I would like to know how you would find the time when you subtract a time that is over 24 hours. Another words how many hours is it from 6 PM say on the fourth from the current time of 12 PM on the sixth. What I’m trying to do is find out the amount of time between two timestamps on two different days. So if I want to find out how long I was on a camping trip, I left 6 PM on the sixth and came back at 8 AM on the eighth of the month, how would I calculate this?
Hi Shannon! I got a problem in subtracting the time, it comes out a negative answer in number (Minutes) especially if the subtrahend is bigger than the minuend.
I am getting different formats for my times. For example some areas are showing 1:03 pm and some are saying 13:03. How can I correct it so everything is uniform?
How does it work with 24hr clock eg a shift worker starting at 11pm (23:00) and finishing at 7am (07:00). I've tried the [h] still can't get it to work :(
Thanks for the tips. Learned something new today.
Glad to hear it! Thanks for watching!
Thank you SO much, I just spent hours looking through articles and other u tube video's getting confused. Then I stumbled across your channel which made my day. You're a great teacher, as the tutorial was concise, informative, and right on point. It's five out of five Gold Stars of greatness from me.
Oh, thank you so much! That is so nice of you. Thanks for taking time to leave a nice comment. I really appreciate it! Glad you found my Channel and that the videos are helpful to you. Take care!
You're the only channel or even resource I found that explains this thank you so so much! Subbed 100% You've saved me from so much stress
Oh, I’m so glad to hear that! I’m glad you found my Channel! Thanks so much for taking time to leave a nice comment and thanks for subscribing!
I love the way you explained this. So simple! Thank you!
I'm so glad you found it helpful! Thanks for watching!
great tip on TIME formula. saved my day.
Glad to hear that! Thanks for watching!
This chanel is needs more viewers. I learned a lot from her tutorial.
Thank you so much! I'm glad you learned a lot from my videos. Yes, I hope I can get more views too!! Thank you!
Hi Shannon. I thank you for explaining the last part. I always had a problem with that.
Glad to help! Thanks for watching!
Thank you very much, very clean and easy to follow.
Glad you found it helpful! Thanks for watching!
Thanks a lot. your explanations are so explicit
Glad you found it helpful! Thanks for watching!
i am glad i found this video……..subscribed….awesome explanation. Thank you
I'm so glad you found this video helpful! Thanks for watching and for subscribing to my Channel! I appreciate it. Take care!
Thanks for your share
Thanks for watching!
That was very helpful, thank you so much
Glad to help! Thanks for watching!
Sharon, finally a good tutorial on how to handel time in excel. I need help on a formula to display time when its past midnight. Here is the issue my server tells me the "minutes since event" when I run an event log in the mornings. I downloaded the event log in excel and added the current time. I subtract the "minutes since event" column and format the cell like this =+$D$1-TIME(0,B10,0) Where $D$1 is the time the log was run and B10 is the minutes since the event. All is good until we pass midnight and Excel has to change fro AM to PM, when this happens, my formula returns the # sign. Can you help me? Some of the minutes on the event log are over a 1000, so that's over 12 hours. Thanks, Mike
Very helpful, but not exactly what I need. I am trying to figure out how to calculate the difference between actual hours worked versus scheduled hours. For example if I am expected to work from 8-5, but only work 10-3. Is there a formula that would do the math and show it as a negative. I was able to use your techniques, but the negative shows as ##########.
Thank you! To calculate the difference between actual hours worked and scheduled hours in Excel, you can use a similar technique to what I showed in the video, but with a few tweaks. If your scheduled hours are 8:00 AM to 5:00 PM and actual hours are 10:00 AM to 3:00 PM, you can use this formula:
=Actual_End_Time - Actual_Start_Time - (Scheduled_End_Time - Scheduled_Start_Time)
However, Excel doesn’t display negative time values by default, resulting in the ########## error. Here’s how to handle this:
Use an IF Formula for Text Formatting:
=IF(Actual_End_Time - Actual_Start_Time < Scheduled_End_Time - Scheduled_Start_Time, TEXT(Actual_End_Time - Actual_Start_Time - (Scheduled_End_Time - Scheduled_Start_Time),"-h:mm"), Actual_End_Time - Actual_Start_Time - (Scheduled_End_Time - Scheduled_Start_Time))
This displays the negative difference as text while preserving the time calculations.
Please let me know if this works for you. Thanks for watching!
Thank you ❤
Thanks for watching!
❤ thanks . I’m looking forward for calculation of OT
Awesome! Thanks for watching! Glad it was helpful.
What a great explanation. Thanks!!
Glad you found it helpful! Thanks for watching!
Hi Sharon, thank you for the great explanation. I was wondering< if you have per say; a flight arrival time and a hotel check-in time and you want to calculate if you'll arrive early or late for the check-in, how you do that? (when I tried, negative times showed the dreaded hashtags) :)
Great video as always. What would the formula be to calculate the difference between the end time of one day with the start of the next day?
Hello! Thanks so much for subscribing to my Channel! If you input the date and time into the cells, and ensure the cell is formatted for Date & Time (or a custom format) then you can simply subtract the end time of day 1 from the start time of day 2 and multiply by 24 to get the result in hours of elapsed time. For example: =(B1-A1)*24 I hope this helps! Thanks for watching!
Love it! Thank you!
Glad to help! Thanks for watching!
Really Helpful
Glad to help! Thanks for watching!
How about that when I have negative hours from last month that are overtaken to the actuall month? Like I have -07:00:00 (-7 hours). I cant just type it into the cell, so the only way to make it, it is a format as number?
Handling negative time values in Excel can indeed be tricky, especially when dealing with hours that span over different months. Excel does not natively support negative time values when using time formats like hh:mm:ss. However, you can work around this limitation using number formats and custom formulas.
Here's a step-by-step guide to handle negative time values effectively:
Method 1: Using Decimal Numbers for Hours
Convert Time to Decimal Hours:
Convert your time into decimal hours. For instance, -07:00:00 can be represented as -7.
Enter Decimal Hours:
Simply enter -7 in the cell. Ensure that the cell is formatted as a number, not as time.
Calculate Total Hours:
You can add or subtract these decimal hours as needed. For example, if you have -7 hours in one month and you want to add 15 hours from another cell, simply use the formula:
=-7 + 15
This will give you 8 hours.
Method 2: Using a Custom Formula to Handle Negative Time
If you prefer to keep using the hh:mm:ss format, you can create a custom formula to handle negative times. Here's how:
Enter Time Values:
Enter your times as usual, but for negative times, you'll need to handle them with a formula. For example, let's say you have 07:00:00 (positive 7 hours) in cell A1 and you want to represent -07:00:00.
Create Custom Formula for Negative Time:
Instead of entering -07:00:00, enter the positive value 07:00:00 in a cell (e.g., A2) and use a formula to convert it to a negative time.
In another cell (e.g., A3), use a formula like:
=-HOUR(A2) - MINUTE(A2)/60 - SECOND(A2)/3600
This will convert 07:00:00 in A2 to -7.
Summing Time Values:
To add or subtract these time values, convert them all to decimal hours first using the formula above, then sum them up.
Example Workbook:
Imagine you have:
A1 with the value 7:00:00 (positive 7 hours)
A2 with the value 07:00:00 (to be treated as negative 7 hours)
In B1, use the formula:
=-HOUR(A2) - MINUTE(A2)/60 - SECOND(A2)/3600
This will convert 07:00:00 to -7.
In C1, you can add these times:
=HOUR(A1) + MINUTE(A1)/60 + SECOND(A1)/3600 + B1
This will give you 0 because 7 - 7 = 0.
Displaying as Time Again
If you want to convert the result back to a time format:
=TEXT(C1/24, "[hh]:mm:ss")
This will convert your decimal hour result back into a time format.
By using these methods, you can handle negative times and perform calculations across different months without the limitations of Excel's time format.
I hope this helps! Thanks for watching!
I understand that Excel Online has less features, but deducting minutes from time yields just number signs (###).
Here are some troubleshooting tips to try:
Adjust Cell Width: Number signs (###) typically appear when the content of a cell is too wide to fit within the column width. Suggest that they try widening the column containing the result to see if the actual value becomes visible.
Check Number Formatting: Ensure that the cell containing the formula or the result is formatted as a time value. Right-click on the cell, select "Format Cells," and choose a time format (e.g., "hh:mm:ss" or "h:mm").
Use Proper Time Format: Make sure that the time values being used in the formula are entered correctly. Time values in Excel should be in the format of "hh:mm" or "h:mm AM/PM" for 12-hour format.
Check Formula Syntax: Verify that the formula being used to deduct minutes from time is correct. The formula should subtract the desired number of minutes from the time value. For example, to deduct 15 minutes from a time value in cell A1, the formula should be: =A1 - TIME(0, 15, 0).
Confirm Cell References: Double-check that the cell references in the formula are accurate. Ensure that the cell reference for the time value and the deduction value are correct.
Check for Errors: If the above steps don't resolve the issue, ask the person to check if any error messages are displayed in the cell. Common errors include referencing empty cells, using incorrect functions, or having circular references.
Test in Excel Desktop: If the person is using Excel Online, suggest that they try the same deduction formula in the desktop version of Excel (if available) to determine if it's a limitation of Excel Online or a specific issue with their formula.
Hope this helps! Thanks for watching my videos!
Can Excel calculate the duration of time over many days? For example, i start my well pump on June 1 at 10 AM and turn it off on June 7 at 3PM. Is there a formula that gives me the amount of hours and minutes the pump was on?
Is there a formula I can use for In time, Lunch Start, Lunch End, Out, In Time #2, and Out time #2
I would like to know how you would find the time when you subtract a time that is over 24 hours. Another words how many hours is it from 6 PM say on the fourth from the current time of 12 PM on the sixth. What I’m trying to do is find out the amount of time between two timestamps on two different days. So if I want to find out how long I was on a camping trip, I left 6 PM on the sixth and came back at 8 AM on the eighth of the month, how would I calculate this?
Hi Shannon! I got a problem in subtracting the time, it comes out a negative answer in number (Minutes) especially if the subtrahend is bigger than the minuend.
I am getting different formats for my times. For example some areas are showing 1:03 pm and some are saying 13:03. How can I correct it so everything is uniform?
Thx 😊
Thanks for watching!
would you provide me sheetlink thanks for sharing awsome tuts
What about if you have 24 hrs shifts. How do you add the hours from one day to the next?
How does it work with 24hr clock eg a shift worker starting at 11pm (23:00) and finishing at 7am (07:00). I've tried the [h] still can't get it to work :(
thanks.
Thanks for watching!
Great,
Thanks so much for watching!
why i can't find (datedif) equation in my excel, even i changed my excel version ?
I’m getting 0 as total hours when I know it’s like 50 😢
Thanks
If total hours are more than 10k then how to calculate time difference for example
10034:34:00
Please help
Nobody noticing she put the characters from Psych in the timecard?
One of my favorite shows! Thanks for watching!
I liked it but it didn't work for me. I tried a few different things. 😢
What are you trying to do? There may be a different formula or scenario that can work for you. Thanks for watching!
Nice
Thanks so much for watching!
how to calculate in 24hours format with result in decimal hours