The article shows a few tips to calculate and display times that are greater than 24 hours, 60 minutes, 60 seconds.
When subtracting or adding time in Excel, you may sometimes want to display the results as the total number of hours, minutes or seconds. The task is a lot easier than it may sound, and you will know the solution in a moment.
How to display time over 24 hours, 60 minutes, 60 seconds
To show a time interval of more than 24 hours, 60 minutes, or 60 seconds, apply a custom time format where a corresponding time unit code is enclosed in square brackets, like [h], [m], or [s]. The detailed steps follow below:
- Select the cell(s) you want to format.
- Right click the selected cells and then click Format Cells, or press Ctrl + 1. This will open the Format Cells dialog box.
- On the Number tab, under Category, select Custom, and type one of the following time formats in the Type box:
- Over 24 hours: [h]:mm:ss or [h]:mm
- Over 60 minutes: [m]:ss
- Over 60 seconds: [s]
The following screenshot shows the "over 24 hours" custom time format in action:
Below are a few other custom formats that can be used to display time intervals exceeding the length of the standard time units.
Description | Format code |
Total hours | [h] |
Hours & minutes | [h]:mm |
Hours, minutes, seconds | [h]:mm:ss |
Total minutes | [m] |
Minutes & seconds | [m]:ss |
Total seconds | [s] |
Applied to our sample data (Total time 50:40 in the screenshot above), these custom time formats will produce the following results:
A | B | C | |
---|---|---|---|
1 | Description | Displayed time | Format |
2 | Hours | 50 | [h] |
3 | Hours & minutes | 50:40 | [h]:mm |
4 | Hours, minutes, seconds | 50:40:30 | [h]:mm:ss |
5 | Minutes | 3040 | [m] |
6 | Minutes & seconds | 3040:30 | [m]:ss |
7 | Seconds | 182430 | [s] |
To make the displayed times more meaningful to your users, you can supplement the time unites with the corresponding words, for example:
A | B | C | |
---|---|---|---|
1 | Description | Displayed time | Format |
2 | Hours & minutes | 50 hours and 40 minutes | [h] "hours and" mm "minutes" |
3 | Hours, minutes, seconds | 50 h. 40 m. 30 s. | [h] "h." mm "m." ss "s." |
4 | Minutes | 3040 minutes | [m] "minutes" |
5 | Minutes & seconds | 3040 minutes and 30 seconds | [m] "minutes and" ss "seconds" |
6 | Seconds | 182430 seconds | [s] "seconds" |
Note. Although the above times look like text strings, they are still numeric values, since Excel number formats change only the visual representation but not the underlying values. So, you are free to add and subtract the formatted times as usual, reference them in your formulas and use in other calculations.
Now that you know the general technique to display times greater than 24 hours in Excel, let me show you a couple more formulas suited for specific situations.
Calculate time difference in hours, minutes, or seconds
To calculate the difference between two times in a specific time unit, use one of the following formulas.
Time difference in hours
To calculate hours between the start time and end time as a decimal number, use this formula:
To get the number of complete hours, utilize the INT function to round the decimal down to the nearest integer:
=INT((B2-A2) * 24)
Time difference in minutes
To calculate minutes between two times, subtract the start time from the end time, and then multiply the difference by 1440, which is the number of minutes in one day (24 hours*60 minutes).
Time difference in seconds
To get the number of seconds between two times, multiply the time difference by 86400, which is the number of seconds in one day (24 hours*60 minutes*60 seconds).
Assuming the start time in A3 and end time in B3, the formulas go as follows:
Hours as a decimal number: =(B3-A3)*24
Complete hours: =INT((B3-A3)*24)
Minutes: =(B3-A3)*1440
Seconds: =(B3-A3)*86400
The following screenshot shows the results:
Notes:
- For correct results, the formula cells should be formatted as General.
- If the end time is greater than the start time, the time difference is displayed as a negative number, like in row 5 in the screenshot above.
How to add / subtract more than 24 hours, 60 minutes, 60 seconds
To add a desired time interval to a given time, divide the number of hours, minutes, or seconds you want to add by the number of the corresponding unit in a day (24 hours, 1440 minutes, or 86400 seconds), and then add the quotient to the start time.
Add over 24 hours:
Add over 60 minutes:
Add over 60 seconds:
Where N is the number of hours, minutes, or seconds you want to add.
Here're a few real-life formula examples:
To add 45 hours to the start time in cell A2:
=A2+(45/24)
To add 100 minutes to the start time in A2:
=A2+(100/1440)
To add 200 seconds to the start time in A2:
=A2+(200/86400)
Or, you can input the times to add in separate cells and reference those cells in your formulas like shown in the screenshot below:
To subtract times in Excel, use similar formulas but with the minus sign instead of plus:
Subtract over 24 hours:
Subtract over 60 minutes:
Subtract over 60 seconds:
The following screenshot shows the results:
Notes:
- If a calculated time displays as a decimal number, apply a custom date/time format to the formula cells.
- If after applying custom formatting a cell displays #####, most likely the cell is not wide enough to display the date time value. To fix this, expand the column width either by double-clicking or dragging the right boundary of the column.
This is how you can display, add and subtract lengthy time intervals in Excel. I thank you for reading and hope to see you on our blog next week!
192 comments
Hello,
I wish to create a table which contains: A cell with minutes (e.g. 45min) to be added to another cell containing the time (e.g. 10:30) and in another cell it will calculate the answer as new time being the 45min passed after 10:30 (e.g. 11:15), is this possible?
Thanks and regards
Godwin
Hi! If I understand your task correctly, this article may be helpful: Calculate time in Excel: time difference, add, subtract and sum times.
Hi
I have two cells that consist of two different times consisting of five digit hours and two digitv minut and I want to sebtract them but it doesn't work.
A1=10507:54
B1=9962:01
I need A1-B1
Hi! A1 is text. Excel cannot write a number of hours greater than 9999.
I have exported data into a sheet where the the TIME reads Hours[space]mins:sec:?? example: 602 59:00:00
However I would like it to remove the space and delete the extra :00 off the end, to read [hh]:mm:ss only
Please help :-)
Hi! Set the custom time format you want. You can see examples in the article above and also here: Excel time format: 12/24 hour, custom, default. Try to use the time format that you wrote in your question - [hh]:mm:ss
I want to do conditional formatting on cells with time. I have a column that advances time by one minute using this formula =B22+(1/1440). I have another cell that calculates 3 hours from another time entered elsewhere, so that E5 is manually entered as a time by the user, and G5 is =E5+(3/24) to advance G5 3 hours. I want my B column to highlight the minute that matches G5. I can make it work, but for some minutes it is not true. I have compared the values and it seems the code for the time is off for calculating the minute sometimes. For example 0.17430555555555500000 0.174305555555556000 FALSE,
both represent 04:11 in B137 and G5 respectively, but as these are not equal the conditional formatting will not apply.
I have tried advancing time in G5 using +(180/1440) to see if it would improve accuracy and will still have a problem with some cells formatting. I have changed 180 to various lengths of time to see the formatting occur and will notice that it fails at some point in time. Is there a way to overcome or fix this time code discrepancy that seems to be causing the formatting to fail?
Hi! Compare hours and minutes in the conditional formatting formula instead of decimals. Extract the desired data from the number using the HOUR and MINUTE functions.
=AND(HOUR(B22)=HOUR($F$5),MINUTE(B22)=MINUTE($F$5))
This should solve your task.
How to convert 47:00:00
Having the cell's format: [hh]:mm:ss;@
To 47
Having the cell's format: number or text
Hi! To convert time to number of hours, multiply by 24. For more detailed instructions, see this article: How to convert time to decimal number, hours, minutes or seconds.
Reply is Respected
Hi, I have cell G6 with this formula
=HOUR(F6-E6)&"hours,"&MINUTE(F6-E6)&"minutes,"&SECOND(F6-E6)&"seconds"
and cell P6 with this formula
=HOUR((O6-N6)+(M6-L6)+(K6-J6)+(I6-H6))&"hours,"&MINUTE((O6-N6)+(M6-L6)+(K6-J6)+(I6-H6))&"minutes,"&SECOND((O6-N6)+(M6-L6)+(K6-J6)+(I6-H6))&"seconds"
I want to subtract G6-P6 to get remaining hours but I get #VALUE! How do I solve this?
For context cell in G6 formula is for wake up time minus sleep time , that displayed date & time eg 2/3/23 1:30 AM.
While cell P6 total time of deep sleep time happens during sleep. (Deep sleep happens multiple times, in this case 4 times, hence the lengthy formula)
How do I make the formula for total sleep minus deep sleep? That will display hours, minutes & seconds?
I’ve tried TIME(G6,G6,G6) - TIME(P6,P6,P6) but I get #VALUE!
Hello! Your cells G6 and P6 contain text. Therefore, you cannot perform mathematical operations with these values.
I have been trying and trying to subtract a time in a 24 hour period.
I need to subtract 7:14 from 24:00.
Steve
Hello! The answer to your question can be found in this article: Calculate time in Excel: time difference, add, subtract and sum times.
What formula I need to used for this?
Start time Stop Time total
1230 1600 3.5
To convert the time to a decimal number, multiply it by 24. Pay attention to the following paragraph of the article above: Calculate time difference in hours, minutes, or seconds.
how can i add them in hh:mm:ss
00:01:13
00:01:12
00:00:02
00:00:02
we can say they are call duration by a given candidate and we want to add total time
Hi!
The answer to your question can be found in this article: Adding and subtracting time in Excel.
Hi!
But the actual problem is that the excel is not taking it as time or anything instead it is taking it like count.
Even after using format cell option i'm not able to solve it using it.
Though I solved it by changing it into seconds first, then again changed it into hh:mm:ss format then it worked
Hi! If your data is written as text, use the TIMEVALUE function to convert it to time.
Thanks a lot that helped in most smooth way!
I wants to convert Time column which is in 1 hr, 2hr, 3hr, etc and wants to convert it to 12:00, 12:05, 12:10, 12:15 like in 24 hours format.
in short to convert the number of hours to time measurement in 24 hour format.
Hi! If I understand your task correctly, the following tutorial should help: How to convert numbers to time format in Excel.
Hi everybody
I have an excel template to calculate enzyme activity. This is the formula they used to convert enzyme activity measured activity in 30 minutes to activity per hour:
=C51*2*Scale/concentration/dilution factor/A/24
where A is the difference between the starting time and the measuring time
1:31:44 PM-1:00:30 PM=00:31 (A=0:31 minutes)
my question is related to this part /A/24
why they divided the values by 24?
I would be very thankfull if someone can help me.
Bests
Hello!
If A is the number of hours, then dividing by 24 converts to the time format. See here for more details: How to convert numbers to time format in Excel.
A is 30 minutes
Hi All,
At the excel sheet, I want to change time format, for example, from 1:00PM of 12 hour format in to 13:00:00 of 24 hour format. I tried all the options at "format cells" but i could not succeeded. Can any body remind me if i missed something or help me please?
Thanks!
Hi!
We have a special tutorial on this. Please see: Excel time format: 12/24 hour, custom, default.
I hope it’ll be helpful.
1M
1M
45M 10S
2H 28M 21S
-
-
3H 20M 14S
I want an average sheet like the above data in excel (for full column)
Hi, can please assist if i got the below info
07:27alt enter18:04 in one excel
how can i separate it in to excel,
i wish to separate it out to two excel in time format so i can subtract it to calculate the working time and calculate any OT allowance
Hello!
Please have a look at this article: How to split string by line break in Excel.
Then use the TIMEVALUE function to get the time.
=TIMEVALUE(LEFT(A1,SEARCH(CHAR(10),A1,1)-1))
=TIMEVALUE(RIGHT(A1,SEARCH(CHAR(10),A1,1)-1))
I hope this will help.
Total activation duration (minutes)
18504:06
which formula to do use to convert this unto hours, minutes & seconds ?
Hi!
I recommend reading this guide: How to convert time to decimal number, hours, minutes or seconds in Excel.
Try this formula:
=INT(LEFT(A1,SEARCH(":",A1)-1)/1440) & " days, " & HOUR(LEFT(A1,SEARCH(":",A1)-1)/1440) & " hours, " & MINUTE(LEFT(A1,SEARCH(":",A1)-1)/1440) & " minutes and " & RIGHT(A1,2) & " seconds"
The [h]:mm:ss fix doesn't work, completely. While it displays correctly in the cell, if I double click it to edit it it reverts back to "1900-01-02 00.32:02" and it says so in the form editor window above the sheet section before and after double clicking. This is only on cells that have more than 24 hours. The rest look fine either way.
Hello!
Unfortunately, I was unable to replicate your issue. Maybe this article will be helpful: Excel AutoCorrect: how to customize or turn off.
To replicate this, do not use the custom number format on cells with formula. Use it on blank cells and then enter the value manually in those cells.
For example, let's say column C is where the user is required to enter the number of hours and minutes spent on a task - manually. So if I spent 35 hours and 15 minutes on that task, I will enter 35:15.
Now, we want that to be entered exactly as such and to stay in number format (so sum function should work in the end to calculate the total of column C) in hours and minutes format. So here text will not work and numbers will also not work as that will not convert the minutes into hours properly. Decimals will also not work here.
So is there a way to do this in excel?
Hello!
When editing a cell, Excel automatically changes the format to date-time. However, you can manually revert the format to [h]:mm:ss. This does not affect the calculation of the sum of time and the sum is calculated correctly.
Hello. I am experiencing the exact same issue as Meet detailed and is not resolved by changing the format to [h]:mm:ss.
I've entered 71:48:00 into a cell. Excel shows this as 23:48:00, even when manually changing the formatting. This means all calculations based on the time are calculating on 23 hours, not 71. Are there any other tips to force Excel to show the actual time rather than 23:48:00?
Hello!
I can't guess why this [h]:mm:ss format is not working for you. Try using Format cells - Time, and select the format 37:30:55. Hope that helps.
I need to work out a formula to count down a 24hours clock example
Start time end time time elapsed Time remaining Time clock 24 clock runs out
19/10/22 - 00.55hrs 19/10/22 - 12:05hrs 12hrs 00 mins 12 hrs0 mins 20/10/22 - 00.55hrs
28/10/22 - 1500hrs 28/10/22 - 2100hrs 6hrs 00 mins 6 hrs 0 mins 28/10/22 - 2100hrs
30/10/22 - 1700hrs 30/10/22 - 2100hrs 4hrs 00 mins 2 hrs 0 mins 30/10/22 - 1100hrs
31/11/22 - 1700hrs 2 hr 0 mins 31/10/22 - 1900hrs
This will continue for a further 4 lines of code to.
Each entry is linked to the previous entry to count down the 24hrs clock
Does anyone know how to write this formula please
thanks
Hi!
Unfortunately, I can't understand what you want to count. Describe in detail what result you want to get from your data. As it's currently written, it's hard to tell exactly what you're asking.
i'd like to start a count down of hours left - 3000 hours minus what cell B2 shows. Can you please share how to do this?
much appreciated.
Hi!
Excel stores times as numbers. Use normal value subtraction.
I recommend reading this guide: Calculate time in Excel: time difference, add, subtract and sum times.
thank you!! :)
I have a report that gives seconds and I need an excel formula that will display that as HH:MM:SS - Anything over 86400 displays improper number of hours. Thanks
Go it - Divide by 86400 and format as {h}:MM:SS -
Hi!
Did you use the [h]:mm:ss time format as recommended in the article above? How many hours do you get and what do you want to get?
Hi, I've date-time format like 15-03-2020 01:50, Now I want to display the time as 13:50 and so on.. the problem is these times are autogenerated and don't have AM or PM. How I need to accomplish.
Hello!
The date and time format you are using is set here: Control Panel - Time & Language - Region - regional format data. Change the time format here as needed. Also, take a look at the article: How to change date format in Excel and create custom formatting.
I hope I answered your question. If something is still unclear, please feel free to ask.
Thank you very much for explaining this topic in a very simple way!
This helped me alot. :)
Hi,
Iam working in aviation field, because of that i need to calculate the larger values in hours.
For eg: 12346:00 - 12345:00, I know that answer should come as 1 hour.
Instead, its showing as "#VALUE!".
Please suggest some formulas for the same.
Hello!
Unfortunately, Excel does not allow you to enter more than 4 characters in the number of hours. Your values are written as text.
Thank you for that,
Is there any other way to track these hours calculation.
For example, if the current value is 12345:6 hours, but after each flights these values need to be updated.
Thank you in advance.
Hello!
You can get the number of hours from a string using the formula -
=--LEFT(A1,SEARCH(":",A1,1)-1)
The number of minutes can be extracted by the formula
=--MID(A1,SEARCH(":",A1,1)+1,10)
Here is the article that may be helpful to you: Extract number from text string.
Hi, I have same issue, working in aviation and want to add/subtract aircraft flying hours/mins using excel e.g
52166:40 - 52163:49
I see your previous response above but dont understand how extracting th number of hours and minutes is the solution. Please advise if there is a way to do this calculation and show correct results in hh:mm format .
Hi!
Split the text into 2 cells as described in this tutorial: Split string by comma, colon, slash, dash or other delimiter.
Then, in each cell, use the formulas described in the comment above.
Hope this is what you need.
Hi,
Please suggest formula to get working time beyond 24 hrs. Like in time 5:50 am in 21st jan, Out time 8:24 am next day. I need a formula which display total working hrs like 27 hrs 34 min.
Hi!
Try to carefully read the first paragraph of this article above.
Hi is there any error in my message? which article you are suggesting? please share the link
Its not working. calculation includes midnight span.
IN TIME OUT TIME TOTAL WORKING TIME
5:50 AM 8:24 AM 2:34:00
I NEED TO PLAY 27[H]:34 [MM] in total working time cell.
I used =IF(BN7>BO7,BO7+1,BO7)-BN7 this formula but its not working beyond 24 hrs.
Hi!
As far as I can see from your second comment, your task is now different from the original one. To get a difference of more than 24 hours, you need to use the date and time, not just the time.
Is there a formula to take total hours worked in a year but only change the format for minutes to a decimal?
Ex: 574:40 ([h]:mm cell format
convert to 574.67 hours
* I want to keep the hours the same but convert the number after the : to a decimal
Thanks
Hello!
Use the TEXT function to get the number of hours and the MINUTE function to get the number of minutes.
=(LEFT(TEXT(A1,"[h]:mm"),SEARCH(":",TEXT(A1,"[h]:mm"))-1))+MINUTE(A1)/60
This should solve your task.
i have clock in and clock out as am to pm in one row like wise for a week ,i need to add total no of hours for that week FOR EXAMPLE
ram
9:30AM-2:30PM
3:20PM-9:40PM
1:30PM-9:50PM IN THIS WAY PER WEEK
HOW TO USE FORMULA PLZ HELP ME.
Hello!
To convert text to time and find the difference, use the formula
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(A1,SEARCH("-",A1,1)+1,50),"P"," P"),"A"," A")) -TIMEVALUE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,SEARCH("-",A1,1)-1),"A"," A"),"P"," P"))
Then you can find the sum of these values.
I have a spreadsheet with accrued vacation leave and accrued floating holiday totals available. I need to let the employee know how much time they have to use by the end of the year so they don't loose any time. They can carry over 160 hours into the new year. Currently one field is formatted as text and the other total is formatted custom ( [h]:mm;@)
Hi!
Your description assumes that I know your details. But I don't know what "floating holiday totals" means, which field is formatted as text, and what is written in it. Describe in detail your data and what you want to calculate.
How do I calculate time remaining till 40 hrs.
For example: I have clock in, clock out, lunch, and total time for the day. Clock in 8:00, clock out 4:30, lunch 0:30, total time 8:00. Then I have SUM total time Mon-Fri in hr:mm.
How do I calculate 40 hr - current total time to see how many hr:mm I have left till I’m working 40 hr?
Thanks for your help!
Hi!
Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data.
Without seeing your data, I can suggest the following formula:
=TIME(40,0,0)-SUM(D2:D6)
Hii sir i need total hrs calculattion like this example. 102hrs:45mns + 01hrs:10mns = 103hrs:55 mns how to use the formula in excel
Hello!
Write these values in two cells and then calculate their sum.
i need the duration formula for 23:40 to 02:00
Hi!
Read the answer to your question in this comment.
If I have an end time of 9:00am and I need to subtract 160 minutes to get a start time, how do I do that?
Hi!
Pay attention to the following paragraph of the article above: "How to add / subtract more than 24 hours, 60 minutes, 60 seconds"
Alexander,
I can't get it to work, I receive the Value error.
Hi!
The #VALUE! error means your time is written as text. Use this instruction to convert text to date or time.
26 Hours 70 Minutes 70 Seconds
Hi!
Please remember of the time units. since it can be only up to 60 mins and 60 secs.
27 Hours 11 Minutes 10 Seconds
Hi,
How to filter data between date and time range as below:
From Date:
To Date:
From Time:
To Time:
I have four criteria to extract data from that.
Please advise.
Kiruban
Hi!
Write down the date and time in separate columns. To separate time from a date, use the formula
=A1-INT(A1)
To filter the values you want, use these guidelines: Excel Advanced Filter – how to create and use.
I hope my advice will help you solve your task.
Hi, can you help me, i want to change 12 hrs to 24hours for example [hh:mm am - hh:mm pm] all these are in one cell [hh:mm:ss - hh:mm:ss]
Hi there, My system produces a data dump and shows a booking timeslot in the following format. 1330-2030 .I am currently using =RIGHT(D2,4) and =LEFT(D2,4) to get the values of 1330 in a seperate start column and 2030 in the end column.
However there is colon : in this data, how can I
A) Convert this into time
B) Do a calculation on excel so that I can subtract both values (end time and start time) to get the booking time of 7 hours.
Hello!
Here is the formula that should work perfectly for you:
=TIME(LEFT(D1,2),MID(D1,3,1),0)
=TIME(MID(D1,6,2),RIGHT(D1,2),0)
Hope this is what you need.
Dear Alexander,
Can you please help me to get the below requirement:
Start Time Minutes Time should be
2:00 PM 45 2:45 PM
32 3:17 PM
30 3:47 PM
Hello!
To add minutes to the time, divide the number of minutes by 1440.
=C1+D1/1440
C1 -- 2:00 PM
D1 -- 45
Hi,
How do I subtract 30 minutes from 00:00 (12 midnight) using excel formula?
Hello!
The formula below will do the trick for you:
=IF(A1<A2,A1+1-A2,A1-A2)
I hope I answered your question. If something is still unclear, please feel free to ask.
Sir
For time comparison in a column the time format is in [H]:MM:SS and for to get results using IF function for greater than 24 hours as beyond time and less than 24 hours what formulae is to be used. Please reply sir.
Hello!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.
Ex if we have times in columns like 34:34:43, 46:57:54, 12:32:56, 44:56:32, 08:45:46 and they are in time format. I want to get result using IF function as for greater than 24 hours to come as "Beyond time" and less than 24 hours as "Within time". Please text the formula and reply sir
Hi!
If I understand your task correctly, the following formula should work for you:
=IF(A1>1,"Beyond time","In time")
Hello, in continuation to above query, how can we use if condition for time format that is [h] beyond 24 hours format. Ex. If [h]:mm:ss - 44:19:23 is greater than 2 hours or not.
Hello!
Time is a common decimal number in Excel. If 2 o'clock is written down as the number 2, then convert it to a decimal number, as described in this article.
Then just compare the two numbers.
I hope I answered your question.
The time is calculated on condition that is from 6am to 8pm so it not 24 hours… so what shall be done in this case. That is [h]:mm:ss is 44:20:19 is calculated from networking days and business hours time… now how to convert it in decimal.. please help sir
Hi!
If you read my answer, then I re-recommend the instruction how to convert time to decimal number in Excel. The article How to use IF function in Excel will also be helpful.
If I’ve to use if formula for : cell A2 value is 44:20:12 (format of cell value is [h]:mm:ss. Now wish to check if condition : =if(A2>=2 hours,“met”,“not met”)
Please advise how to check the time in [h] format using if condition.. please help
I have a spread sheet one cell has employee’s dispatch time (military time) example 23:59 (11:59 pm) the next cell has employee’s arrival time example 00:20 (12:20 am).
Dispatch on one day, arrives a few minutes later (21 minutes) the following day.
I have figured out the rest of the spread sheet with your help, however these two cells, as soon as I calculated them I get the death formula symbol of ####### any help would be appreciated.
Thanks for your valuable time. Pat
Hi,
The answer to your question is already on the blog. Read here.
Good evening sir,
I have downloaded a file from online and the time is in the format of 000:56:30, 0101:34:66, 132:43:00, 000:34:32, 0345:56:34, 0056:12:35 and so on in this format. While selecting the columns the task bar is showing count of numbers only but not sum of the time and the average. I have used TEXT(Value,"[H]:MM:SS") and after that used TIMEVALUE of that function and it is showing as #Value. I have tried using TIME(HOUR,MINUTES,SECOND) it is showing as #value. Please solve the above problem. I will post u file to Email also if you provide email. Pls solve it.Sir,
I want to get total sum and sorting of these column in ascending or descending order sir.
Hi,
To convert the text 0345: 56: 34 to time, convert the text to a number. For example, if this text is written in cell A1, use the formula
=--A1 you have given this reply sir.
Sir I have used the formula it is giving the result #Value. Please provide your email I'd so that I can send file to you sir. Or please send me an email so as to send the file to you sir.
Hi,
Read this comment.
I was able to come up with this formula however now I cannot find my duplicate. I think I've been looking at this for too long. Any suggestions??
I'm coming up with: 2 months 18 daysG 18daysJ 22 hrs 18 min
IF(L76936<60,TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysA ""h ""hrs"" m ""min""")),IF(L76936<3600, TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysB ""h ""hrs"" m ""min""")), IF(L76936<86400, TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysC ""h ""hrs"" m ""min""")), IF(L769361," daysD ", " dayE ") & TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysF ""h ""hrs"" m ""min""")), ROUNDDOWN(L76936/2592000,0) & IF(ROUNDDOWN(L76936/2592000,0)>1, " months ", " month ") & ROUNDDOWN(MOD(L76936,2592000)/86400,0) & IF(ROUNDDOWN(L76936/86400,0)>1," daysG ", " dayH ") & TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysJ ""h ""hrs"" m ""min"""))))))
Thank you
Did come up with this formula but now get a preceding 0 in days when I have 685984 seconds. I would like to eliminate the 0.
Result: 07 days 22 hrs 33 min
IF(ISERROR(IF(L76953<60,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")))),0,IF(L76953<60,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")),IF(L76953<3600,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")),IF(L76953<86400,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")),IF(L769531," months "," month ")&TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")))))))
And 2424119 seconds displays as 028 days 1 hrs 21 min
Could you please assist? I am trying to convert seconds to months, days, hours and mins however if it cannot be converted to months then days hours and mins will do. Currently, I have 6819518 seconds which comes to 2months 18days 22hours 18min or 78days 22hours 18min. This is the formula I have currently am using however it becomes an issue when there is more than 30 days. I tried changing 86400 to 2592000 but not yielding the results I need. Any help is greatly, greatly appreciated!!
IF(ISERROR(TEXT(A1/86400,CHOOSE(MATCH(A1,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""days ""h ""hrs"" m ""min"""))), 0, TEXT(A1/86400,CHOOSE(MATCH(A1,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""days ""h ""hrs"" m ""min""")))
Hello!
To convert seconds to days, hours, minutes and seconds, use these formulas:
days (in G3)
=INT(E2/(60*60*24))
hours (in H3)
=INT(E2/(60*60))-G3*24
minutes (in I3)
=INT(E2/60)-G3*24*60-H3*60
seconds (in J3)
=--RIGHT(E2,2)
I hope it’ll be helpful.
Good evening sir,
I have downloaded a file from online and the time is in the format of 000:56:30, 0101:34:66, 132:43:00, 000:34:32, 0345:56:34, 0056:12:35 and so on in this format. While selecting the columns the task bar is showing count of numbers only but not sum of the time and the average. I have used TEXT(Value,"[H]:MM:SS") and after that used TIMEVALUE of that function and it is showing as #Value. I have tried using TIME(HOUR,MINUTES,SECOND) it is showing as #value. Please solve the above problem. I will post u file to Email also if you provide email. Pls solve it.
Thank you.
Hello!
Explain what result you would like to get from these text values - 0101:34:66, 132:43:00, 0345:56:34 ?
Sir,
I want to get total sum and sorting of these column in ascending or descending order sir.
Hi,
To convert the text 0345: 56: 34 to time, convert the text to a number. For example, if this text is written in cell A1, use the formula
=--A1
Please check out this article to learn how to convert text to number with formula and other ways.
Sir I have used the formula it is giving the result #Value. Please provide your email I'd so that I can send file to you sir. Or please send me an email so as to send the file to you sir.
Hello!
Use the following formula:
=IF(LEFT(A1,2)="00",--MID(A1,3,20),--A1)
Please note that 0101:34:66 is not the time. There are no 66 seconds.
Sir
I want to have the sum of those column in time format and want to have sort in ascending or descending order
I am looking to identify the total minutes for the following that has been split into days, hours, minutes, seconds: in the format of 01:04:56:05
01 - in theory 24 hours
04 - 4 hours
56 - minutes
05 - seconds
I should be able to see: 1736 minutes
Hello!
To convert hours into minutes, you must first multiply the specified time by 60 (the number of minutes in one hour), then by 24 (the number of hours in one day). In other words, we need to multiply time by 1440.
I believe the following formula will help you solve your task:
=TIMEVALUE(MID(A1,4,20))*1440+60*24*(--LEFT(A1,2))
Hope this is what you need.
How to add 6 hour 24 min 40 secs + 2 hours 39 min 30 secs
Hello!
Please have a look at this article — How to sum time in Excel
I hope my advice will help you solve your task.
How easy was that...?! Beautifully explained and so easy to follow. Thank you for the first rate help.
Hi guys,
I have a start time of 18:00 and end time is 01:00 the next day, when I calculate = End time - Start time I get a negative value of -17 instead of 7 hours . Which formula to use to to get the correct positive value of 6?
Thank you after struggling to find it in the Excel help. 30 seconds of searching on Google and reading your article fixed it.
Hi excel specialist
How can I change from excel format from 24-00 to 24.00. what is the formula
Pls. advise thanks in advance hope to hear from anyone.
Hello!
I recommend that you study the recommendations on how to change the time format in Excel.
I hope this will help, otherwise please do not hesitate to contact me anytime.
Setting up a time card, I have 9 columns, 3 sets of IN time and OUT time. I can get columns to add up using a 24 hour format when it's a low vs a high hour such as IN:0600 / OUT:1600 for a total of 10 hours. However, what I can't figure out is how to formulate In:2000 / OUT:0600 for a total of 10 hours as the amount of time worked was 10 hours.
I know out how to get it to auto sum hours worked when I format the cell (mm/dd/yyyy h:mm) but that over populates the time card and makes it a lot harder to read. Is there a way to auto calculate the total hours worked using (h:mm) and auto sum the 3 column sets into a single total hours worked regardless of what day and time they clocked in?
My crews work 24 hour rotating shifts logging between 8 to 14 hours at a time. They also check out between the start and end of the shift and have to clock back in.
For Example; IN-19:00 (Begin Shift) OUT-24:00 (30 Min Meal) / IN-00-30 OUT-0330 (30 Min Rest) / IN-0400 OUT-0600 (Time to go Home) Total time worked: 10 Hours
I want to set up a time card they can just put in the time they clock in and the time they clock out to simplify their lives. It'll also help keep from displaying unnecessary data for our finance clerk and speed the processing of payroll up.
Hello Rick!
If the end time is longer than the start time, then you need to add 1 day to their difference
=IF(A1 > A2,A2-A1+1,A2-A1)
If you need the number of hours to be more than 24, use the custom format [h]:mm:ss.
I hope this will help, otherwise please do not hesitate to contact me anytime.
Kindly help this condition
my job time 9:00 am and grace time start 9:15 am
I want to calculate 9:16 am to start late time in minutes
reply