## Calculating Elapsed Time in Excel

Let’s say that to make a point about the relationship between hot water usage and the size of the utility bill, you want to track how long your teenager spends in the shower. Or, in planning for a lawn care business, you want to track times required to mow lawns of various sizes. Excel would be a good tool for these kinds of tasks.

Excel’s ability to perform complex calculations is well known, but asking it to return simple time calculations can produce confusing results unless you understand all the factors involved.

At the heart of date/time values in Excel is the fact that they are stored as serial numbers that indicate days and decimals of days since the beginning of January, 1900. Excel refers to these as serial numbers, but they are not serial numbers as you may be used to seeing them as a mix of letters and numbers. They are instead numbers stored in a dddd.tttt format, where the digits to the left of the decimal point tell the number of days since January 1900 and the decimal tells the part of the day that has passed since midnight. This format is known as “serial date” or “serial date-time”. If you enter the current date and time in a cell and set the format to General or Number, you will see the date’s serial number version. April 13, 2009 1:39 pm is represented by the number 39916.56875. Understanding how Excel looks at dates and times can help us to demystify time calculations.

In this example I wanted to know the elapsed time between the first and second values:

As you can see I wanted to calculate how much time elapsed between 8:30 AM and 10:30 PM by subtracting the 8:30 start time value in cell A2 from the 10:30 PM end time in cell B2. The start and stop times were entered with AM and PM formats. Behind the scenes, Excel interpreted the start and stop times in terms of a 24 hour clock and saw the problem this way:

22:30 – 8:30 = 14:00

By default, Excel applied the AM/PM format to the result and returned 2:00 PM which is 14:00 on a 24 hour clock. So it calculated the correct answer – 14 hours of elapsed time – but I want to display that answer in hours and minutes.

To fix this problem, give cell C2, the result cell, the preferred format. Open the Format Cells dialog box and choose the Custom category on the Number tab. Then in the choices at the right, choose the h:mm format. The difference or elapsed time is 14 hours.

Note that the same problem with the start/end times entered with 24 hour time shows how Excel worked with the AM/PM time entries.

What does Excel do when the ending hour is actually a smaller number than the starting hour? This type of situation occurs when a worker begins a shift at 11:00 pm and ends at 7:00 am the next morning. What is important to remember in this time calculation is that while our focus might be on the number of hours and minutes that have elapsed, the number describing the day has also advanced by one as well.

In the example below you can see the result of the simple formula to subtract an evening start time from a next morning end time. The formula returns a negative number so Excel displays the #### symbols.

You can use the IF function to accurately calculate spans of time that stretch from one day to the next . The formula below contains the simple subtraction of the start value from the end value; but it also tests, using the IF function, the value of the end time to see if it is smaller than the start time. In cases where the end time is smaller, 1 (the value of another day) is added to the result.

Another surprising result can appear when adding several amounts of time that total more than 24 hours as in the example below:

You can see that the intent is to show total hours worked in cell B6 using the Sum function. It is easy to see by quick estimation that the total of hours worked was more than 9 hours and 45 minutes. Once again this is a cell formatting problem.

Excel seems to assume that you know these amounts add up to a day or days and some amount of hours and minutes more than a day. Given the current format applied to cell B6, Excel is just displaying the “hours and minutes” part of the answer. To specify your preference to display the entire time here, including the days, expressed in hours and minutes, you must use square brackets in the format. The number of hours and minutes in this sum will show in cell B6 if we apply the custom format of [h]:mm.

Remember when you work with time calculations in Excel:

1. consider how Excel is storing the time value in memory – as a serial decimal number and

2. make sure that the cell format displays the time as you intended

May 7th, 2009 at 12:17 pm

Lets say that I have a time of 9:33:26 am, now I want to subtract 6 minutes and 33 seconds. How do I get that new time?

May 11th, 2009 at 6:44 am

Gordon,

If you have the time 9:33:26 am in cell A1 and you have the amount of time to subtract entered in cell B1 as 00:06:33 (no hours, 6 minutes and 33 seconds), then you can enter the formula in cell C1: =A1-B1. Excel returns the time of 9:26:53 am.

May 30th, 2009 at 7:41 am

I have been using excel for many years and never knew that it was this simple, just a custom format! I had always tried the days360 hour, minute, month and composed a complex formula. Thanks so much for enlightening me.

July 15th, 2009 at 9:46 am

Veronica,

Hope you don’t mind someone from outside the University…

I have what seemed to be a very simple conversion from decimal hours to hours and minutes, and can’t seem to make it work. The formal is a straight forward rate/distance = time. The ‘time’ in this case has been formatted as [h]:mm. What begins as 1.85 should format to 1:51; but, that’s not what is being displayed. When the [h]:mm format is applied, Excel returns 44:24.

Can you offer any advice or assistance?

July 15th, 2009 at 1:05 pm

Frank,

Glad to hear from you.

I converted your 1.85 time to hours and minutes using the text function. Placing the value 1.85 in cell A1, I then put this function in cell B1: =TEXT(A1/24,”h:mm”)

I hope this helps!

Veronica

September 7th, 2009 at 9:27 pm

Oh, I am using Excel 2007 and I have 2003 at work.

September 14th, 2009 at 9:51 am

Hello,

I need to subtract race times. I have a runner that runs a 5K race in 19:06:97. Another runner runs it in 25:01:97. A third runner does it in 29:09:07. I need to subtract the second time from the first time. Then the third time from the second time etc. This had nothing to do with the time of day but Excel can not seem to get around this.

I set up a form to do this but I am having trouble keeping the time format so the cells show the proper times. I keep getting 7:06:90 PM

Also, when I try to subtract 25:22:96 from 24:40:12 ie 25 minutes, 22 seconds and 96 hundredths of a second from 24 minutes etc. Excel can not do this and give me a #VALUE! error. Thank s for your time with this. I really would appreciate the help. My daughter runs in college and I am trying to keep her (and the teams) time

I am using Excel 2007 at home and 2003 at work in case your solution will only work with one of the other.

Evan,

I was able to do the calculation by the following method:

1) I selected the cells that hold the time (in my sample B2 – faster runner and B3 – slower runner).

2) I opened the format cells dialog box. In 2007, Home tab/Number group/click drop-down arrow next to the default format/click More Number Formats in the list that appears. In 2003, click the Format menu, click Cells, click the number tab.

3) In the list of categories on the left, I chose Custom. Then in the list at the right I found one to match the type of values in this scenario: mm:ss.0 (Be sure that the punctuation between seconds and tenths of seconds is a period – not a colon.) If you don’t see that choice in your list, you can create it by typing that in the box right beneath the work “Type:”

4) Then in the cell that will hold the calculation, I put this formula: =TEXT(B3-B2, “mm:ss.0”)

I hope this gives you what you need. And good luck to your runner!

Veronica

October 4th, 2009 at 10:49 pm

Hello Veronica,

Unfortunately, no. I am either not doing what you said the way you said or it is not working with the times that I am using.

Can you try these exact times for me?

Cell A1=26:56:97

Cell A2=25:56:97

The answer should be a difference of 1 minute. I need the answer to look like this: 1:00:00. Maybe Excel can’t make these calculations without creating a VB program which I dont know how to do. If you make this work, maybe you can send me an example spreadsheet in my email. I think you are my last hope.

Thanks again.

October 8th, 2009 at 8:15 am

Evan,

The values you sent me have two colons separating them. The third segment of your time should be expressed as a decimal like this:

Cell A1 = 26:56.97 (which would indicate 26 minutes, 56 seconds and 97 one-hundredths of a second.

Cell A2 = 25:56.97

When I entered these values with the decimal points between the last two numbers the formula works.

Veronica

October 12th, 2009 at 12:27 am

Veronica, this worked great for me. I really needed to do a time study w 100ths of a sec. Great job!

October 26th, 2009 at 4:03 am

Hi, Vernoica!! Thank you for solving a long standing issue I had with our schedules when inputting out times that went past midnight. Folks would forget to type in 25:00 for 1:00 pm which caused problems for our calculating break sheets.

Of course, I come begging a question as for every action, there seems to be a reaction:

It seems to have an affect on the total net hours column. Your formula as I would adapt for one of our break sheet rows =(C17-B17+IF(C17

October 26th, 2009 at 4:42 am

Just a follow up: I was trying this formula: =(E13

October 29th, 2009 at 11:08 pm

Hello,

Can someone help me with this process. I work in the Air Traffic Control industry (military) and have to calculate Delay Start Times. We use Julian dates and military time. How can I use Excel to create a process to calculate this. Example:

09302 is a Julian Date and 0500 is the military time. I need to be able to subtract 09301 Julian date and 0100 military time from it.

09302/0500

– 09301/0100

How can I do this in Excel 2007. I understand formulas a little bit.

Thanks

Mike

November 3rd, 2009 at 11:51 pm

I found a problem with the elapsed minute calculation using the INT function if the time period wraps over 12:00PM and the duration is 38 to 48 minutes. For example, if 11:30 AM is entered in A1, 12:15 is entered into B1, and =INT((B1-A1)*1440) is entered in C1, it returns “44”. I am confused.

November 4th, 2009 at 11:39 am

Mike,

There is a very good resource for working with Julian calendar dates in Excel at this url:

http://www.cpearson.com/excel/jdates.htm

I hope you find this helpful.

Veronica

November 4th, 2009 at 12:01 pm

Fred,

You should be able to do that calculation with the simple formula =B1-A1. Then do the second step which requires setting the format for displaying the result. To do that go to the Format Cells dialog box, click the Custom category on the left, and choose the appropriate format from the choices on the right. For your example, the mm:ss format works.

Veronica

November 21st, 2009 at 9:40 am

Hai…

I want to record the time in excel sheet. by giving the formula =now(), i can get the time, but i want to get the seconds. I know by formatting the cell i can display the time in seconds. but if i enter the formula in the next cell, whatever time which is recorded in the first cell is also getting changed.

Can anyone help me how to fix this problem. Some of my friends told that it is possible by using VB. Unfortunately i am not good at VB. Can anyone help me.. If anyone knows the solution, please mail me at jashinpepsi@gmail.com

Thanks and Regards

Jashin Jayson Anthikad

Hinduja Global Solutions

November 30th, 2009 at 11:12 am

Jashin,

From what you describe, it sounds like Excel is simply recalculating each time you enter the =now() formula. So the first cell is automatically updating to give the correct value of “now”.

If what you are trying to do is to capture a start time value and an end time value for calculating a difference between them, you may want to do that with VBA. You can find one solution to this task at this site:

http://en.allexperts.com/q/Visual-Basic-1048/calculate-difference-start-date.htm

I hope I have interpreted your question correctly.

Veronica

December 10th, 2009 at 11:28 am

Easily, the article is actually the greatest on this deserving topic. I agree with your conclusions and will eagerly look forward to your coming updates. Saying thanks will not just be sufficient, for the wonderful lucidity in your writing. I will immediately grab your rss feed to stay informed of any updates. Authentic work and much success in your business efforts!

December 18th, 2009 at 6:11 am

Dear All

i want to jusk ask you in easy words calculate diffrence between two defrent date with diffrent time

December 23rd, 2009 at 11:02 am

Am now getting back to the project noted a few posts earlier. I am trying to automate a certification worksheet we use for calculating continuing education units (CEUs) based on total minutes. Sessions are designated in one of four competence categories, so a day-long workshop may have sessions for different categories. We have to total up the minutes for each category, then assign CEUs for the entire day. For example, 30 to 45 minutes is 0.5 CEU, 46 to 75 minutes is 1.0 CEU, 76 to 105 minutes in 1.5 CEU, etc. The problem is when the calculation for a 45 minute session wraps over noon, it only reports 44 minutes using the INT function. I realize that whether the INT calculation reports 44 or 45 minutes, our CEUs remain the same – but there are perception issues with the certificants. If there is no solution, we will just deal with it, but this seems a be an odd problem.

December 29th, 2009 at 6:39 pm

Hi Veronica! This is a great site! My question has to do with converting time elapsed into the minutes:seconds format. I export this data from a report, and while in the report, it shows a time elapsed of 9:45 (mm:ss), but once I export it as Excel or text, the time is converted to 585.0078125. Is there a way to change it back to 9:45? Thanks.

December 31st, 2009 at 12:20 pm

The formula returns a negative number so Excel displays the #### symbols.

goto —> tools —> options —-> calculation —-> and turn on the 1904 date system.

Now you can go backwards into time., be sure to set your format for the cells as [h]:mm:ss

January 4th, 2010 at 8:10 am

Fred,

What is happening behind the scenes with the INT function is that while the result of 44 is displaying, the actual value Excel is hanging onto includes decimal places that are not displaying. If you type 11:30 am in cell a1 and 12:30 pm into cell b1 you can then format each of those cells to a number with 9 or so decimal places to see the underlying value of that time (i.e. what part of a day that time represents). Using the INT function as in:

=INT(J3-I3)*1440 yields the result of 44. But if you remove the INT function just leaving J3-I3*1440 and increase the number of decimal places for the result cell if necessary, the result displays as 44.9999999 for as many places as you have chosen to display. Modifying the formula to keep the INT function but round the two time values to 8 decimal places each like this: =INT(ROUND((B2-A2),8)*1440, I get the result of 45 for the elapsed time.

January 4th, 2010 at 8:14 am

Glenn,

Can you tell more about this work flow? What kind of report are you starting from?

Veronica

January 4th, 2010 at 8:29 am

Here are a couple of things you can use. The first example is assuming time/date values in cells A1 (earlier date) and A2 (more recent date):

In the cell where you would like the result type: =INT(a2-A1)&” “&TEXT(A2-A1-INT(A2-A1), “hh:mm”)

The next approach gives a result in years, months and days plus part of day and has the earlier date/time in cell A3 with the more recent in A4:

=YEAR(A4)-YEAR(A3)-IF(OR(MONTH(A4)<MONTH(A3),AND(MONTH(A4)=MONTH(A3), DAY(A4)<DAY(A3))),1,0)&" years, "&MONTH(A4)-MONTH(A3)+IF(AND(MONTH(A4) <=MONTH(A3),DAY(A4)<DAY(A3)),11,IF(AND(MONTH(A4)=DAY(A3)),12,IF(AND(MONTH(A4)>MONTH(A3),DAY(A4)<DAY(A3)),-1)))&" months, "&A4-DATE(YEAR(A4),MONTH(A4)-IF(DAY(A4)<DAY(A3),1,0),DAY(A3))&" days"

I hope one of these is helpful.

Veronica

January 5th, 2010 at 1:27 pm

Veronica,

I have a “NetworkDays” function dilemma. I am trying to calculate the elapsed time in days as measured by 24 hour increments between two dates formatted as m/d/yyyy/h/m. In my problem example, a case was entered at 12/14/2009 18:23 and was responded to at 12/15/2009 15:22 – so in 0.87 of one day or less than one day which is what I’m trying to measure. The NETWORKDAYS function returns a value of 2 days, probably because it is counting the number of working days.

I need to measure the elapsed time in terms of days but also exclude weekends and holidays. Can you help guide me to an appropriate function/formula combination?

Thanks,

Scott

January 5th, 2010 at 3:45 pm

Scott,

The NETWORKDAYS function is counting both your start date and your end date as working days in this example. It does not consider fractions of a day. Excel eliminates from the result automatically any days that are weekends – you don’t have to specify those. If you want it to leave out holidays, you have to identify those and include them as the third argument for the function. So if your company has 9 annual holidays, you could list those in a range of cells (cells A1 to A8 for instance). Then if you have a start date in cell A10 and an end date in cell B10, the result cell should hold: =NETWORKDAYS(A10,B10,A1:A8)

However, if I understand your question correctly, you want to both automatically eliminate weekends and consider parts of days in your calculation. I found this expression that you might try, but check it carefully to see if it gives you what you need:

=NETWORKDAYS(A5,B5)-1-MOD(A5,1)+MOD(B5,1)

This example assumes a start date and time in cell A5 and an end date and time in cell B5. Check the source of this formula at http://www.exceltip.com/st/Calculating_the_Number_of_Weekday_Hours_Between_Two_Dates/870.html

I hope this helps.

Veronica

January 5th, 2010 at 8:15 pm

Hi, Was wondering if you can help with the following:

I would like to subtract the number of Hours:Minutes:Seconds from a date in Excel.

For Example I have one cell which has a date of “8/01/2010 15:08″ and another cell with the amount of time ” 15:51:59″ I would like subtract 15 hours and 51 minutes and 59 seconds from the date and time of 8/01/2010 15:08 to produce a date and time in Excel. Have tried a number of things but have not had much luck.

Any ideas?

Thanks

Michael

January 6th, 2010 at 8:37 am

Michael,

Try converting each of your values, the date and the amount of time to be subtracted, to a number. Then do the subtraction. Finally convert that answer back to a date format.

Veronica

January 6th, 2010 at 8:58 pm

thanks Veronica for your help

January 9th, 2010 at 10:00 pm

Thanks for the explanation about subtracting times. I want to calculate the difference in minutes between successive days and I would like to know if it’s a positive number or a negative number. But it seems to require that you always subtract the smaller number from the larger (e.g., using MAX MIN, otherwise you get an error message. Is there some way around this? I want to be able to plot the changes in time. Thanks!

Teya

January 11th, 2010 at 9:06 am

Teya,

Could you expand a bit on what you are trying to do?

Veronica

January 13th, 2010 at 7:03 pm

How can I calculate the difference between these two dates in EXCEL .. 19-Dec-09 10:44 AM and 20-Dec-09 1:22 PM — I have about 100 dates and times — need to find a way to do it — thanks

January 14th, 2010 at 9:02 am

Marjorie,

If you typed the first time in cell A1 and the later time in cell B1, in cell C1 you can place the formula =B1-A1. If you then set the formatting of cell c1 to a custom format to make the answer display as hours and minutes, your example shows the result of 26:38:00 or 26 hours and 38 minutes.

Veronica

January 14th, 2010 at 11:51 am

Hey —

I typed it in — 12/19/09 10:44 AM in A1, 12/20/09 1:22 PM In B1 — formated C1 as HH:MM and I get 2:38 as an answer .. not 26.38 — is there something else I should have set ?? Thanks

January 14th, 2010 at 1:23 pm

Marjorie,

In the custom number formats, the one you want is [h]:mm:ss. Or leave off the :ss if you aren’t interested in seconds.

Veronica

January 14th, 2010 at 4:14 pm

Thanks so much — it working just fine now — save a lot of time and energy. Have a great afternoon.

Thanks again

February 3rd, 2010 at 12:21 pm

I am trying to write a work schedule that will let me know how many hours will be worked. I would like excel to tell me that 9:30-5:30 is eight hours. My thought was to have an in, out and total column ie b3 is in b4 is out and b5 is total. Because this schedule is then sent to all employees I need it to be in a 12 hour format. Any ideas

February 9th, 2010 at 10:59 am

Jeff,

From what you describe, it sounds like you want to enter your times in B3 and B4 so they show in AM or PM format. You should just be able to enter them that way (i.e. 9:30 AM or 5:30 PM) to designate the format. Then when you do the subtraction calculation in B5, be sure cell B5 has the format of [h]:mm:ss so the result shows length of time. To set that format, go to the Format Cells dialog box (Home tab, Number group, click the small arrow icon in the lower right corner of that group). On the Number tab of the Format Cells dialog, click the Custom category on the left, and choose the [h]:mm:ss format on the right.

Veronica

February 12th, 2010 at 4:42 pm

Veronica – just stumbled on this site and wow it helps –

would like to check and ensure i’m doing this correctly:

A1

2/11/10 5:00PM

B1

2/11/10 5:12PM

From your example I use the formula = =(A1-B1)+IF(A1

February 15th, 2010 at 9:43 am

Troy,

When you are including the entire date and time in each cell, you should not need to use the IF function. The date part of the value is making B1 larger that A1. But if you simply had to times only and the period spans midnight, e.g. start time is 5:30 pm and end time is 1:30 am, adding the IF statement is necessary.

Your plan for finding the average of lengths of time should give you what you are looking for.

Veronica

March 22nd, 2010 at 12:10 pm

Hello,

I came across this website while trying to average elapsed race times. I have over a hundred times that are formatted as minutes and seconds it took to finish the race. See example:

22:10

22:35

30:42

33:47

I have formatted the cells as Custom, using mm:ss. When I try to average them I am getting a DIV error. Can you help?

March 25th, 2010 at 9:07 am

Cindy,

There are a couple of issues with this calculation. First is that even though you format a cell as mm:ss, Excel still interprets the value before the first colon as an hour. So if you just type in 22:10, it translates that value as 10:10 pm or 22 hours, 10 minutes, 0 seconds. To correctly enter the minutes and seconds you intend, you must also enter 0 for the hour portion of the time: 0:22:10.

As far as the DIV error is concerned, I was only able to replicate that in a situation where I was trying to calculate the average of values that had been formatted as “text”. Could that be what has happened in your cells?

Hope this helps,

Veronica

March 25th, 2010 at 9:12 am

Hi,

I am not sure how hard this is but I need to calculate time lost and time gained.

say for example i need the below result over different days

16/03/2010 02:00 15/03/2010 23:00 difference -3hrs

16/03/2010 23:00 17/03/2010 02:00 difference =3hrs

What formula do I use to get this? can you help?

Thanks

March 25th, 2010 at 10:58 am

Each of these dates and times has a serial value assigned to it by Excel. March 16, 2010 2:00 am is equal to 40253.08333. Your March 15 day and time is 40252.95833. So calculating the difference between your two dates is the simple subtraction of those two values (formatted as date/time works; Excel is working with the serial values behind the scenes). If the later date/time is in cell A1 and the earlier date/time is in B1, you can enter the formula =A1-B1 in cell C1 to see the result of the subtraction. Cell C1 should be formatted as the custom format [h]:mm:ss or something similar depending on whether you care to see seconds or not.

However, Excel does not deal with negative time values. So if you try to subtract an earlier date/time from a later date/time the result cell will be filled with the # symbol.

March 26th, 2010 at 8:40 am

Thank you so much for your help on this. I teach Excel and other programs to employees at my workplace and really want to figure this out. When I tried adding the “0” in front of the times, I still received the DIV error, so I tried changing the formatting from mm:ss to h:mm:ss. Do you think I am choosing an incorrect format?

March 26th, 2010 at 8:47 am

It’s strange, but when I retyped the times in a new spreadsheet, it works. I’m not sure what the difference is, but am happy I have it working. Thank you!

April 20th, 2010 at 10:01 pm

Thanks so much for this information, I am not a very experienced EXCEL user and have been struggling to get these time calculations for a long time. I have start / end times and totals reflecting hours worked. Now I want to calculate daily/ weekly and monthly earnings. I tried setting up cell with hourly rate and used hours worked cell product but this did not work. How should this cell be formatted? Thank you for your assistance. I am trying to calculate wages for clients that must stay under SGA wages to maintain SSD disability benefits.

April 21st, 2010 at 7:45 am

Marta,

Can you give more information about where your calculations break down? Are you getting an error when you try to find total pay (total hours worked * pay rate)? Or are you getting an answer that doesn’t make sense?

Veronica

April 21st, 2010 at 4:43 pm

I am getting an answer that doesnt make sense – despite reading the above comments I am not making the connection in how to format the destination cell ( total pay).

April 23rd, 2010 at 8:34 am

Marta,

When I place a start time in cell A1, say 8:00 AM, and an end time in cell B1, perhaps 3:00 PM, I can perform a simple calculation in cell C1 to find the difference: =B1-A1. Even though I give cell C1 the custom format of [h]:mm to force the formula result to display as a number of hours, Excel is still holding on to the value in cell C1 as a part of a day or .29166667 of a day.

When I then try to multiply in cell E1 the hours worked from cell C1 times the employee’s hourly rate which is sitting in cell D1, Excel uses the .29166667 value in that calculation. So I am multiplying perhaps $9.00 * .29166667. To convert that decimal expression of part of a day to hours, multiply it by 24. So the formula in cell E1 should say: =(C1*24)*D1.

The above formula works if you pay for every minute. If you pay for full tenths of an hour you could use the TRUNC function to calculate their time worked and have it expressed to the tenth of an hour so that cell E1 would say: =TRUNC(C1*24,1)*D1. The second argument of the TRUNC function tells how many decimal places to include in the result.

I hope this information is helpful.

Veronica

April 26th, 2010 at 8:58 am

Success! Thank you very much for your help – I have been struggling to capture this info in a spreadsheet for a long time. You have made it easier for many of my clients to avoid overpayments to SSA.

July 8th, 2010 at 6:27 pm

I’m trying to convert hours, minutes and seconds into total minutes and rounding up the seconds to a minute. However, when my report shows that someone has more than 24 hours…my formula doesn’t calculate correctly. =roundup((hour(b2)*60+minute(b2)+second(b2)/60),0)).

I’ve looked at the custom formatting and read the help section that only offered that if an AM or PM appears in the function line, then it means that the format is set to a 12 hour clock….but I can’t seem to find any information that will change the format to a 24 hour clock and NOT show the AM/PM in the function line.

Is there anyway that I can convert hours over 24 to total minutes and hours under 24 to total minutes with the same formula? Currently, I’m manually calculating those that appear on the report with hours over 24 and using the above formula for those that have under 24 hours. I hope that someone can help me!!!

July 9th, 2010 at 3:53 pm

Teresa,

I tried to simulate your problem with these results:

In cell A5 I entered 30:28:45 with the cell formatted as [h]:mm:ss. (The formula bar still shows this as a date and time in January of 1900 but that doesn’t interfere with the result.) In cell B5, I used the Insert Function tool to choose the Roundup function. (In using Insert Function, it can be helpful to see the preview of values as you work and judge whether you are getting the correct result as you work.) So when I entered cell A1 as the location of my number to be rounded, I could tell at a glance that Excel was seeing only 1 hour plus a part of another hour. It is automatically clipping off the other 24 hours. So as in one of the other examples in this article, I multiplied the value times 24 and also multiplied by 60 to capture the minutes in each hour. In the Num_digits field I entered 0. The result showed 1829 minutes in 30 hours, 28 minutes and 45 seconds.

Veronica

July 21st, 2010 at 4:31 pm

Hi Veronica,

I am trying to calculate difference between two time values. Ex:

Job A was supposed to complete at Tue 1:30 AM. It completed at 11:57 PM on Monday night itself. I want the result to say – 1:33 (I understand I’ll need to convert it to decimal value equivalent of this time).

Similarly, Job B was supposed to start at Mon 10:30 PM. It actually started at Tue 12:15 AM. I want the result to say it was late by 1:45 (again, equivalent decimal value should be fine).

Could you please help. I actually need to plot chart showing a trend of the expected and actual values.

Thanks!

July 29th, 2010 at 11:16 am

The first task of calculating the difference between actual and expected is easy to do with an IF function that tests whether the actual is greater than the expected. So if A1 holds the expected start and B1 holds the actual start, in C1 you could place the function: =IF(A1>B1,A1-B1,B1-A1). The problem arises in attempting to express a time result as negative. Excel won’t do that. If all you wanted to do was to see those negatives, you could apply conditional formatting to cell C1 to indicate the fact that the actual (B1) is less than the expected (A1). But if you want to calculate further with the value, you might try copying cell C1 and doing a paste value in cell D1. Then in cell E1 put the formula =IF(A1>B1,-D1,D1). That is pretty clunky, but if it gives you what you want you could look further at automating those steps.

Veronica

October 8th, 2010 at 3:56 pm

Veronica,

I am trying to figure out what formula or even how to track compensatory time lost. For example, we allow employees to accure comp time each month; however they must use it within a year of accruing it. Employees track time/leave in an Excel workbook (one file) which includes each month as a it’s own sheet/tab. I have formulas set for each person’s sheet that shows time used and accrued. Is there a formula that I can add to each monthly sheet to show if comp time is lost that month due to not using enough time throughout the year? I really appreciate your assistance!

October 22nd, 2010 at 9:31 am

Lisa,

This solution might do what you want. Essentially for each month track “Comp time used” and “Comp time earned” as it appears you are doing already. Then use each of those cell references for a 12 month period to calculate “Comp time available”. Perhaps something like =(Feb!C2+Mar!C2+Apr!C2+May!C2+Jun!C2+Jul!C2+Aug!C2+Sep!C2+Oct!C2+Nov!C2+Dec!C2+JanNext!C2)-(Feb!B2+Mar!B2+Apr!B2+May!B2+Jun!B2+Jul!B2+Aug!B2+Sep!B2+Oct!B2+Nov!B2+Dec!B2+JanNext!B2) where cell C2 in each sheet holds “Comp time earned” and cell B2 holds “Comp time used” might work. Each new month this formula would drop the first month’s cell reference inside each set of parentheses and add a new month’s reference to the end.

I’m not sure I have completely grasped your situation, but this might be something to try.

Veronica

November 22nd, 2010 at 12:00 pm

Veronica,

I am attempting to assist my coach with creating a training plan for our college rowing team. I would like to be able to input an athlete’s time in A1 (formatted as mm:ss.0) then in B1-C1 have percentage values of that time. For example, I would like to enter 2:00.0 into A1 and have B1 display 95% of A1, C1 90% of A1, etc., etc. This would save us a lot of manual computation/data entering. Is there a way for Excel 2007 to handle this? Thank you so much for your help.

November 29th, 2010 at 11:05 am

Dan,

It should work for you to enter your time durations in column A (cell A1 for this example) and then in column B (cell B1) enter the formula =.9*A1. You might need to change the format of cell B1 to the custom format of mm:ss.0 to get the display you want. Then of course in cell C1 you could enter the formula =.95*A1.

Veronica

December 1st, 2010 at 5:12 pm

Thanks!!!

December 5th, 2010 at 9:12 pm

Hi V,

Im trying to do a simple calc for the below,

0700 to 1115 = 4.15

However, I would like it to display 4.25 (4hrs 25mins) instead of 4.15 (although it works well for 0700 to 1100 = 4.00). How can I do so?

Thanks in advance!!!

December 6th, 2010 at 8:41 am

Wendy,

The key to time display in Excel is the format used in the cell. If you type the time in with a colon separating the hour and minute, Excel will recognize it as time. But you can also go to the cell format dialog box and specify exactly the display you want. With the cell selected, you can use the shortcut CTRL+1 to open the dialog box. In the category column choose Custom. In the column on the right select the [h]:mm:ss format (to display a number of hours, minutes and seconds. But if you only want hours and minutes, in the “Type:” field just delete the “:ss.”

I hope this helps.

Veronica

December 6th, 2010 at 7:13 pm

Hi V, thanks for the above.

However any chance of NOT using the semicolo but a . instead, and still get the answer?

Eg. 7.00 11.15 4.25

Thanks again!

December 7th, 2010 at 8:40 am

Wendy,

There isn’t an easy way to do that because format is such an important factor in how Excel interprets the value in the cell. When I type 7.00 in a cell and give it a time format, Excel translates that value into the date 1/7/1900 12:00 AM. The value of 1 in the system Excel uses is assigned to Jan. 1, 1900 which makes 7.00 equal to Jan. 7, 1900.

The decimal value of 7:00 am is .291667 which you can see by typing 7:00 am in a cell and changing the format to general. I don’t know if that process holds any potential for what you want to do, but it seems it would be awkward to work with as well.

Veronica

December 7th, 2010 at 5:43 pm

Hi V, got your points. Thanks heaps for the explanation!

January 17th, 2011 at 6:26 pm

Hi Apologies for hijacking this thread but I am desperate for help. I am trying to include a formula for calculating time taken (in business hours) between two points. For example in cell A1 I have 16/01/2011 09:30:00 and B2 I have 17/01/2011 11:15:00. What formula will calculate this in business hours assuming they are Mon-Fri 08:00 – 17:00.

January 18th, 2011 at 11:32 am

Hi Andy,

This is tough because while Excel has the NETWORKDAYS function that will calculate whole days between two dates, I know of no function that breaks that down into hours and minutes. Here’s an approach you might play with. It is many steps so maybe a macro could help. It seems you need to capture for each part of the calculation, the value (date and time) of day’s beginning and day’s end for each date in your range. If the employee began work at 9:30 on Jan. 16, 2011 and that date is sitting in cell A1, in another cell you could use one of the Rounding functions to calculate just the date portion of the starting information. Then if you add to that number the serial value for 17:00, quitting time that day, you have the start time and end time for that day which can be subtracted to see the number of hours and minutes worked that day. You could do the same process for the end date only you would be using the value for 8:00 am added to the date portion of the serial value of the end date. Once you have the amounts for each day, you can total them.

Does anyone else out there have another approach?

March 18th, 2011 at 12:17 pm

Veronica,

I have read this thread and cannot seem to get my display correct. I am trying to calculate the elapsed hours from 3/1/07 at 12:26 to 3/6/07 at 10:15. I have separate columns with the decimal date.time format and am subtracting B1-A1. It returns 21:49 in the custom h:mm format and 1197:49:00 in the [h]:mm format. I know that I should be getting around 118 hours. I don’t understand what I’m doing wrong.

Thank you in advance!!

March 18th, 2011 at 1:08 pm

Abby,

When I entered your values and your subtraction formula, with the result format cell formatted as [h]:mm, I get 117:49. (I guessed that 12:26 was pm and 10:15 was am.) When I change the date/time values to a general format the start time is expressed as 39142.51806 and the end time shows as 39147.42708. My result expressed in the general number format is 4.909027778. But, again, when I set that result cell format to the [h]:mm format it gives me 117:49. You might compare your general format values to these and see if Excel is interpreting your original date/times correctly.

Veronica

March 18th, 2011 at 1:42 pm

I checked it all and my start time is 391420.51806, end time also has an extra zero, so my result is 49.90902778. I tried changing the format of the times and dates. I don’t know if it makes a difference, but I had to concatenate my date and time columns to set it up this way.

I cannot thank you enough for responding so quickly!

March 18th, 2011 at 2:04 pm

Ah, the problem is in the concatenation. You want to ADD the date value to the time value. The difference is that in concatenation the date value, 39142, is being “glued” to the time value, 0.42708333. When you concatenate, Excel thinks you want to keep the 0 in ones place as part of the concatenation result. So the date/time value becomes 391420.42708 instead of 39142.42708.

If you add the date value to the time value instead of concatenating, you should get the correct value for each date/time.

March 18th, 2011 at 3:13 pm

Veronica,

Amazing! Wonderful! You have made my day. Thank you for a great site and for your help!

June 3rd, 2011 at 3:59 pm

Hello,

I am trying to calculate time usage with the time being military time.

Example: (columns: start end start end overtime

0000 0800 1600 0000 .5

I need the total hours worked between 0000-0800 plus 1600-0000 minus overtime .5 then the total count has to be divided by 4.

Can you help? I am so stumped!

Thank you

LeNor

June 6th, 2011 at 5:11 pm

LeNor,

Check this out to see if it gives the result you are looking for. I am assuming in the example you sent that the worker has been on call from midnight to 8:00 AM and from 4:00 pm until midnight. The worker put in 1 hour and 30 minutes of on duty time in that range. In my spreadsheet I have entered the various start and end times in columns D through G of row 5. So in cell H5 I have entered this formula: =((D5-C5)+((1+F5)-E5))-G5 where D5-C5 calculates the hours before 8:00 AM, (1+F5)-E5 calculates the hours from 4:00 to midnight, and subtracting G5 takes away the time that will count as overtime. As in all calculations with time, it is important to have cells formatted appropriately to make the calulations work.

Veronica

July 28th, 2011 at 12:01 pm

Hi Veronica,

I am looking to do kind of the opposite here. I’ve got a start time of 3:13:42.000 and then a column of elapsed times (in seconds) ranging from 0.385 to 118822.847. I know I can’t just add the elapsed times to the start time because they aren’t really in a time format. I have formatted the start time to HH:mm:ss.000 and added 0.385 seconds to it, with a result of 12:28:06, which is not correct. I know I need to somehow format the elapsed times into hh:mm:ss.000 and add those to the start time, but even then there is the issue of keeping the 24 hour requirement. I hope this makes sense and you can give me a tip or two…

Thanks!!

August 3rd, 2011 at 7:30 am

Iris,

You might try this. I entered a start time in cell A1 with the cell set to a custom format of h:mm:ss.000. In cell B1, I entered some random number of seconds. Cell B1 was formatted as General and displaying three decimal places. In cell C1, I entered the formula =B1/86400 with 86400 being the number of seconds in a day. I also formatted cell C1 with the format of dd:hh:mm:ss.000. The effect of cell C1 is to convert the number of seconds in B1 to days, hours, minutes, seconds and part of seconds. Then in cell D1, I could do the math to add A1, the original time, and C1, the amount of time to add. Cell D1 was also formatted as dd:hh:mm:ss.000.

Veronica

August 27th, 2011 at 5:03 am

Thanks for everthing Veronica,I applied the formula =(b2-a2)+if(a2>b2,1) but unable to calcculate the overtime. what if the over time is only recognized after the completion of 9 hrs in cell c2(hours worked).

thanks

Subash

August 30th, 2011 at 7:41 am

Subash,

I think the problem arises from the underlying value that is returned by the formula. It is the decimal part of the day. To convert that value to a number that you can continue to use in other calculations, multiply it by 24. So my formula in cellD2 for calculating hours worked, when start time is in B2 and end time is in C2, is =IF(C2>B2,C2-B2,(C2+1)-B2)*24. Then in cell E2 you could calculate the hours of overtime with this formula: =IF(D2>9,D2-9,0). That will place the number of overtime hours in the cell if there are any, and place a 0 there if there are no overtime hours. I named another cell on the sheet PayRate in which I entered the employee’s rate of pay and used that value in this formula to calculate pay in cell F2: =(D2*PayRate)+((E2*1.5)*PayRate).

September 2nd, 2011 at 3:25 pm

Hello Veronica.

Looking for answers for a worksheet I am planning I came here and first just wanted to thank you for your wonderful blog (website?). I am so glad I found it!

I want to build a worksheet that can tell my daughter when she needs to go to sleep, wake up, etc’, after she keys in the time her bus picks her up and all the stuff she needs to do until then.

So I tried to deduct from the time (let’s say 7:15 am) 10 hours sleep, 20 min shower, etc’ etc’ and… of course it didn’t work.

I don’t know if I need to find the right format for the cells or the right formula (I guess both), but I would appreciate very much if you could refer me to where I can educate myself, so I know how to do it by myself, or help me doing it.

Thanks a lot and have a great long weekend.

Saul

September 2nd, 2011 at 3:55 pm

I am having a hard time converting times. I have a start time of 8:31:00 AM in cell A1. I have a duration value of 106M which represents minutes. I need to determine the end time. When I use the custom format to change 106 hh:mm format in show a date of 4/15/1900 00:00 AM. I can manually change the date to 01/01/1900 1:46:00 AM and add it to the start time and get 10:17:00 AM. I don’t know what I’m missing.

September 2nd, 2011 at 11:10 pm

Saul,

In columns A through C (or however many columns it takes), I would enter the variables: Sleep Hours, Breakfast, Shower, etc. Those cells would have the custom format [h]:mm. Remember that a 30 minute breakfast would be entered as 0:30. In the next column I calculated Total Time Needed which is the sum of all those variables, also formatted with the custom [h]:mm format. In the next column I simply entered the Bus Time formatted as a time of day. In the next column I calculated the Bed Time using the formula: =(Bus Time + 1) – Total Time Needed. The formula adds one to the Bus Time because it is on a day later than the Bed Time. That cell is also formatted as a time of day. Lastly I calculated Alarm Clock Setting with the formula: =Bed Time + Sleep Hours with that cell also set to a time of day format.

I hope I have interpreted your project correctly and that this is helpful.

Veronica

September 2nd, 2011 at 11:46 pm

Jennifer,the

Try this. Enter Start Time in A2 formatted with a time of day format and Duration in B2 formatted as [h]:mm custom format. In the next cell enter a formula that will convert Duration to part of a day: =B2/60 with that cell formatted as Number. Then in cell D2 enter the formula =A2+C2 and format that cell with a time of day format.

Veronica

September 7th, 2011 at 7:07 pm

Hello Veronica,

“I hope I have interpreted your project correctly and that this is helpful.”

Yes, you certainly did and it WAS helpful.

Everything works as it should now and I appreciate that ALOT and am very thankful!

I now have some more questions, if I may (again, I spent quite a long time in the Internet trying to google the answers but…. no avail):

1- Would you recommend any printed material and/or source in the Internet, where I can understand this basic stuff? (Books I purchased/websites are either too general and superficial, or too advanced). I purchased the book Office 2011- I have a Mac, but it just explains very basic stuff) IE what is the difference between [h]:mm and h:mm format and many other questions in this basic level (like the ones following).

2- Now that this is working (thanks to you) I would like to improve it a bit. I am building this for a child with learning disabilities and would like to save the input writing “0:30” for 30 min, “0:05” for 5 min and so. Is there a way that she can just write in the appropriate cell the absolute # (30, 5, etc’) and I do the calculations in a different column (a hidden one). For this purpose I would need to “translate the 5 into 0:05 and so, and I have no clue if is possible, and of course that, if it is, no idea how to do it).

3- I would like to make one cell flashing, so it will stand out (either the text, or the background, or the border, or all of them).

4- I have a question regarding conditional formatting. Should I start a new thread/join an existing one with that subject?

Well, again thanks for what you have done and thank you in advance for my other additional stuff here.

Saul

September 8th, 2011 at 10:01 am

Saul,

1. I think the explanations of “Mr. Excel”, Bill Jelen, are well done. He is online at http://www.mrexcel.com and has Excel training in a variety of formats. Another good source is John Walkenbach at http://spreadsheetpage.com.

The square brackets around any element of a time, whether hours, minutes or seconds, stipulate that given a number of that element in brackets ([h] for instance) that would be enough to form “one” of the next larger element (e.g. 25 hours = 1 day and 1 hour), you want to see a display of that total number of hours. If you add a column of seconds and the total of that column would be more than a minute, you could use the custom format of [ss] to force the display to show total seconds – even if that total is more than 60. Without the square brackets, the result would show as minutes and seconds.

It is worth noting, since you mention that you use a Mac, that the Date System in Excel for the Mac uses January 1, 1904 as the serial value of 1 instead of the Windows date of January 1, 1900.

2. You could set this up with a column labeled hours (column A for this example), one labeled minutes (B), and one labeled seconds (C) – although I am guessing that most of her tasks are going to be minutes long which, if true, would allow us to simplify this. I’ll show that later. Assuming longer tasks and using the three columns described above, she could enter the hours in column A, the minutes in column B and so forth. In each case she is just entering a number. Then in column D, you could place a function that looks like this: =TIME(A2, B2, C2). This function looks in A2 for the hours, B2 for the number of minutes, and C2 for the number of seconds. You want the format of cell D2 to be the custom h:mm:ss format.

If it is likely that all tasks will be minutes in length, you could just provide a column A to enter the minutes and alter your function (now in cell B2) to supply automatic zeros for the number of hours and seconds: =TIME(0,A2,0) but keep the cell formatted with the custom h:mm:ss format. So her entry of 30 in cell A2 would display as 0:30:00 in cell B2.

3. Excel 2010 does not have a built-in capability to make a blinking value or background. However I did find this VBA code that might do what you want:

http://www.cpearson.com/excel/BlinkingText.aspx

4. If you don’t see anything you are looking for on conditional formatting in the IT Training tips existing posts, you can always go to our home page, http://ittrainingtips.iu.edu, and click the link at the right under the heading Have a Question?

Veronica

September 12th, 2011 at 9:43 am

Hello Veronica,

Thanks for your clear clarification.

1- I have to apologize. The answer for my question was just in front of my eyes, (like many other in our lives) :-), I just needed to look.

YOUR explanation, at the top of the page was enough for me to understand. When just came to this blog I looked for a specific answer, so I just ran through the tutorial, never really reading it.

2- Tried, works, problem solved. Again, thx, very clear and easy to understand (see, I understand very fast if you explain it to me very slow)…

I am SO thankful for your help!

3- Tried, went to the website, tried to copy the code. Of course doesn’t work. For me VBA is like Chinese (I am not too good in Chinese) :-). Maybe soon I can find some time to try to start learning the basics. In the meantime I’ll live with a non-blinking cell, and so is my daughter going to.

4- Thanks for referring me there. I didn’t see the answer to my question, so I will post my question as soon as I finish this project.

And now to this project, (I think/hope my last “hurdle” to finish it):

I need to know if there is a way to do the following:

My daughter can enter 2 different times in EITHER D2 OR D3.

D25 and D26 are for entering hours and min.

If she enters the time in D2, then she cannot enter any values in D25 or D26.

If she enters the time in D3 than she MUST enter a value in either D25 or D26, or in both.

I have already “used” the validation feature in those cells (is there a way to do more than 1 validation in a cell?).

Again, thanks for your past help and in advance for this one.

September 12th, 2011 at 8:42 pm

Correction:

I don’t know if it matters, but regarding my last question, instead of entering hours and min in D25 D26 should be D25 E25

September 13th, 2011 at 8:27 am

Saul,

Can you describe a little more about the nature of what she might be entering in D2 or D3 and what is then happening in D25 and E 25?

Veronica

September 13th, 2011 at 9:17 am

Sure. Sorry if I missed this information.

In D2 or D3 she enters either the time she needs to be ready for the bus (D2) OR the time she needs to arrive at her destination if she gets there by herself (D3). Those 2 cells are validated with the formula =COUNT($D$2,$D$3)<=1, so she can enter input only in one of the 2 cells and formatted as 12 hr time.

D25 is for entering the hours it will take her to get to her destination if she gets there by herself and not with a ride (when she enters the time in D3) and so is E25 but this is for the minutes. Those are formatted as general and validated so that the hours are limited to only 1 and the min cell limited to 55.

So I want her to be forced to enter a time in either D25 or E25 if she enters the time in D3, but not allow any enters in those 2 cells if she takes the bus (enters time in D2).

I hope this is clear enough. If any additional information needed please let me know.

Thanks and have a wonderful day.

September 13th, 2011 at 10:51 am

Veronica,

hi,

i formated a worksheet for time record/sheet data as date, name, start time, end time, hours work

i am not able to build a formula that a worker starts from day 01 – say 10.00 Hr to 20.50 Hr and on Day 03 his duty is from20.50 pm to 7.30 Hr

I have about 200 – 250 workers — need to find a way to do it

and I update this worksheet on daily basis so that at month end exact calculation can be done.

Can you help me & How can I do so?

Thanks,

Santosh

September 13th, 2011 at 2:06 pm

Hi Veronica

I’m trying to calculate the delay before a result is authorized. In column F is the date authorized with the time authorized in column G. The date resulted is in column D and the time resulted in column E. Dates are currently formatted as xx.xx.xx and times as xx:xx:xx, although I don’t need the seconds. I’ve calculated the time delay using G-E, which is fine if both events occur on the same day, which they don’t always. In those case I’ve manually worked out the delay, input the result e.g. 25:39:00, which excel converts to 01:39:00.

How do I combine both the time and date to work out the delay? I’m using Office 2003 at work and 2007 at home.

Your help would be much appreciated.

September 13th, 2011 at 3:36 pm

Saul,

Perhaps this might work. One approach could be to use Conditional Formatting in cell D3 so that cell D25 is highlighted when cell D3 has a value entered. To do that, with cell D25 selected, Go to Home/Conditional Formatting/ New Rule/Use a formula to determine which cells to format. In the Format values where this formula is true: field, type: =OR(D2=0,D2=””). Then click the Format… button and choose a special format for cell D25 that would draw attention to it. Click OK.

As for preventing entry in D25 or E25 if D2 has an entry, click the cell, go to Data/Data Validation/Settings. In the Allow: field choose Custom from the drop down list. Then in the Formula: field that appears type: D2=0. (If I am keeping track of your design correctly, this says you will only allow entry here if D2 has no value entered which means that you are taking the bus.) You could click the Input Message tab and type a title for this message like Riding bus? Then you could enter a message like “Don’t enter time here if you are taking the bus.” This message will only show up when the cell is selected. Then, still in the Data Validation window for this cell, you could click the Error alert tab and choose the Stop style. This will cause Excel to fight any attempts to enter a value in the cell. Here again you could enter a message title and message to explain why a value can’t be entered. You could treat cell E25 the same way.

I hope this is something close to what you are trying to set up.

Veronica

September 13th, 2011 at 3:50 pm

Santosh,

Before I address your solution, I want to be sure I understand how you are entering start and end times. If a worker has a start time 20.50 pm do you mean that he started at 30 minutes past 8 o’clock in the evening or 50 minutes past 8 o’clock in the evening? Do start and end times get entered in tenths of hours?

Veronica

September 13th, 2011 at 10:38 pm

Veronica.

I finished this project!!!

Those 2 last tips you gave me were exactly what I needed!!!

Thanks a lot. I appreciate that very much, thanks to you I could finish it.

I am so glad I asked you for help!

Saul

September 14th, 2011 at 10:17 am

Louise,

I created a spreadsheet following your model with the ‘date resulted’ in cell D2, the ‘time resulted’ in cell E2, the ‘date authorized’ in F2 and the ‘time authorized’ in cell G2. Then in cell H2 I used the NETWORKDAYS function combined with the IF function to calculate the days of delay: =NETWORKDAYS(D2,F2)-IF(G2>E2,1,2). NETWORKDAYS avoids counting Saturdays and Sundays. If that is a poor assumption on my part, you could substitute the formula =IF(G2>E2,F2-D2,(F2-D2)-1)). Cell H2 should be formatted as General. In each of these calculations the subtraction portion accounts for the fact that you don’t want to count both the start day and the end day as full days. To calculate the difference in hours and minutes, in cell I2 use the IF function: =IF(G2>E2, G2-E2, (G2+1)-E2). Cell I2 should be formatted with the custom format [h]:mm.

I hope this is helpful.

Veronica

September 20th, 2011 at 7:17 pm

Hi Veronica, HELP! I am trying to develop a spreadsheet in Excel to track my minutes (sum them at bottom of ea day) AND my pay by the minute (sum them at bottom of ea day). To clarify my situation, I get paid by the minute $.20. I have a program from stopwatch-online.com that tracks my time spent on work tasks in stopwatch time. So, it displays things like 00:00:00 but I only really use minutes and seconds when I enter things in Excel so for example, I enter 11.00 for eleven minutes and 1.10 for one minute 10 seconds and then 1.45 for One minute 45 seconds. The boyfriend pointed out that my formula in the next cell over that calculates pay isn’t really accurate since 1.45 is really 1.75 of an hour right? So, how do I enter my time from the stopwatch program in A and tell Excel in B how to convert the stop watch time to REAL time / minutes and then convert to minutes in C and calculate my pay in column D, what would be the formulas? I am going mad trying to figure this out. My A column where I enter the stopwatch program time is setup with Number 2 decimal places. The pay column I setup with the formula 0.2*SUM(D1:D1).. REALLY appreciate your help with this… -Debby

September 21st, 2011 at 8:49 am

Hi, Debby,

You say that you enter the times in Excel, and it looks like you are entering them using a decimal point instead of a colon to separate minutes from seconds. So part of your fix is to enter 1 minute and 10 seconds, for instance, as 0:1:10 using colons to separate hours from minutes and minutes from seconds. (You do have to enter the 0 for hours even though there aren’t any hours.) You also want the cell formatted with the Custom format of either [h]:mm:ss or [m]:ss. Then, with the time in minutes and seconds sitting in cell c3, to calculate pay you could use this formula: =minute(c3)*.2+(second(c3)/60)*.2

Veronica

September 21st, 2011 at 12:25 pm

Wow! thanks alot Veronica, I take it then there is NO way to keep it as a decimal like format (actually I just checked and it’s entered in Number format with 2 decimal places) and then convert it in another column? No command that tells it to do that I mean? The reason I ask is it is far simpler to data entry it in that way ..it’s faster.

September 21st, 2011 at 1:52 pm

Debby,

Is it that you are having to enter each time manually? And is it that you just prefer hitting the period key than holding down two keys to get the colon? Or is it that you already have a lot of entries and you would like to correct them all with as few key strokes as possible? I’m just trying to get a better handle on what your task is to find a good solution.

If it is that you already have many values entered as decimals, try this formula to convert them: (assuming the value to be converted is in cell A1 and entered as 1.50 meaning 1 minute 30 seconds)

=TIME(0,INT(A1),MOD(A1,1)*60)

The new cell should be formatted with the custom format of [h]:mm:ss.

Let me know if I am misunderstanding your situation.

Veronica

September 21st, 2011 at 3:24 pm

Hi Veronica, Yea, basically I find it easier to enter it as a decimal and enter it straight from the stop watch program that displays 1 minute 30 secs as 1.50, and so 1.30 would be something smaller in an increment of time I think. (horrible at math thank GOD for you lol) .. and yes, I am entering ALOT of entries.. so if there is a way to take that time I’m entering as number with 2 decimals and convert it in another Cell to straight minutes that would be great. However, what I was hoping to find is a way to keep it as I am entering it now basically, so 00:11:34 is 11.34. or Eleven minutes thirty four seconds. Then the next column changes that to ACTUAL REAL time, in other words, so I’m not short changing myself when I compute the pay by *20 the column with the actual time for things such as 11.45 which in real time should be 11.75, know what I mean? thanks.

September 21st, 2011 at 4:32 pm

Sorry Veronica, I think my last response sounded kinda retarded lol.. I’m entering right from a stop watch program from http://www.online-stopwatch.com if it would help for you to see it. It displays things 00:00:00 so unless I’m totally misunderstanding how to read it (possible) 1.30 is less than 1.50 and so on. When I compute things in excel to figure my pay using this data, it appears to be incorrect, due to times such as 1.45 being really 1.75, unless I misunderstand the way the stop watch is displaying time and how Excel computes my numbers.

September 23rd, 2011 at 2:45 pm

Veronica, thanks so much for the formulas you provided. Part of the problem is my misunderstanding decimal entries versus real time entries. I think I have that sorted (you have to convert the 1.30 to what it is actually 1.50). However, what I was looking for in terms of Excel is if there is any faster way to enter the time the stopwatch gives me that you know of.. that would convert the time entered into straight, real time, so it would convert the 1.30 that I enter in column A (either in number 2 decimal form or some other quick data entry like way numbers ) to then have Excel change that time to 0:01:30 in B column then the next column C calculate the pay with the formula you provided: =minute(c3)*.2+(second(c3)/60)*.2 I’d appreciate it. OR if there is even some way to tell Excel for Column A to convert the time in one entry all at once (without having to type the colons) that would be very useful. Perhaps I am asking too much of the program but you seem to know it inside and out and backwards and sideways so I thought to run it by you. Thanks again Veronica, you have really saved my sanity and the day!

September 23rd, 2011 at 4:58 pm

Debby,

My expectation was that your Stop Watch program was giving you the time as minutes and seconds so that would be the values you would enter. But if you are given the time as minutes and decimals of minutes here is what you would do:

1) In column A enter the time as a decimal, for example 1.5 (which would be the same as 1 minute and 30 seconds or 1:30) That cell would be formatted as Number with 2 decimal places.

2) In column B place this formula to convert the decimal value to hours, minutes and seconds: =TIME(0,INT(A1),MOD(A1,1)*60) That cell would be formatted as Custom [h]:mm:ss

3) In column C place this formula to calculate pay at $.20 per minute: =MINUTE(C2)*0.2+(SECOND(C2)/60)*0.2 That cell would be formatted as currency.

Veronica

September 27th, 2011 at 11:38 pm

Hi Veronica and many thanks, Question for you, for some reason with the formula =MINUTE(C2)*0.2+(SECOND(C2)/60)*0.2 it isn’t converting one hour or 60.00 min 0 secs. Any idea why? It will calculate 0:59:00 as $11.80 but it won’t take the 0:60:00 and convert it to $12.00, how come and how can we fix it? THANKS AGAIN SO MUCH FOR YOUR HELP GREAT SITE!!

October 5th, 2011 at 4:48 am

Dear Veronica:

I have: A1= 18:00, B1=19:30, C1=B1-A1, D1=10, E1=C1*D1

The result in E1 should show 15 while it is showing 0.63

How can I multiply the calculated hours with rate?

Regards,

October 5th, 2011 at 10:25 am

Debby,

In the first examples you presented, I guess I assumed that your amounts of times were always less than an hour. So the design we’ve come up with is fighting attempts to work with more than 59 minutes in a couple of ways. 1. Column C is formatted to display as [m]:ss for number of minutes and seconds and assumes that the largest number you would have for minutes is 59. It doesn’t expect ever having more minutes than that. So if you expect to have more than 60 minutes, you will have to use the [h]:mm:ss format to allow for that. Then in column D where you are calculating the pay, you need to include a component to the formula that will convert the hours from column C to minutes and multiply that value by the .20 rate. The pay calculation then becomes:

=HOUR(C2)*60*.2+MINUTE(C2)*.2+(SECOND(C8)/60)*.2

Veronica

October 5th, 2011 at 10:59 am

Abdulhamid,

When you subtract times and get 1:30 for the time duration, behind the scenes Excel is storing that as .0625 of a day. Since your rate is an hourly rate, including 24 in your hours * rate calculation makes the units match up.

The formula in cell E1 should be:

=C1*D1*24

That gives a result in E1 of 15.

Veronica

Veronica

October 12th, 2011 at 12:47 pm

Good morning-

I have a set of time sheets – one which shows the punches entered by the individual and one which shows a set of edited punches. I am trying to figure several pieces of information from the data I have.

Using your above explaination and formulas I was able to figure the total time worked for the edited and unedited time. Now that I have an hour value for total time worked each day (edited and unedited) I want to try and figure out when there was a loss of time through the adjustment, when there was a gain of time through the adjustement. I am just not sure how to set up the formula. Do you have any insights?

October 12th, 2011 at 1:32 pm

Good Day Veronica,

This is a great reference. Your examples above have helped me get to this point in my project but I’m now facing an odd problem.

I have created a simple table for employee scheduling in the form of 6 columns (In / Out – In / Out – In / Out). A final column calculates totals for each row very simply by using ((Out – In) + (Out – In) + (Out – In)); using military times and everything here works perfectly.

I have an additional need to view this data graphically as well. I believe that using the basic chart/table funcitons of Excel won’t work here if an employee shift is split around a lunch/break. I am assuming that a graph may want to list a given employee more than once for each portion of the shift (That and my experience with charts/graphs is nill.)

I have a method to create the desired graphical representation, however I am missing what I believe may be a very simple concept or formula.

On a 2nd worksheet I have created a grid of columns representing time in 1/4 hour increments (4 columns represent 1 hour). I can pull the various start – end times from the first worksheet and calculate a duration between the 2 into additional cells for later reference. The resulting values can be shown in decimal or time depending on the cell formatting.

For example:

Start time: 7:30 am – (0.3125 in decimal)

End time: 12:00 pm – (0.5 in decimal)

Duration (derived using End minus Start): 4.5 hrs – (0.1875 in decimal)

What I’m missing here is how to utilize the values from the first worksheet (start / end / duration – in any combination) to define a range of cells that I would like to have shaded using conditional formatting.

I need to be able to define a range of cells based on one or more of these values. (ie. Each 1/4 hour of duration = 1 cell. So 4.5 hours of duration will = 18 cells (columns).

I believe that am looking to identify a ‘starting position’ cell based on one of the IN times and an ‘ending position’ cell based on one of the OUT times. The resulting range would be all cells in a row between the starting and ending positions inclusive.

Time durations between OUT and IN (lunch break for example) would need to be omitted from any range definition and therefore not become shaded.

What I’m missing is being able to define a cell range based on data from the other worksheet. If you could point me in the right direction it would be greatly appreciated.

Thank you,

October 12th, 2011 at 1:50 pm

Sophia,

Is the time always being edited in the same way? That is, if the employee has worked 5 hrs and 3 minutes, does that get adjusted to 5 hours? Are there rules for how those adjustments are made? Knowing that would help with the solution.

Veronica

October 16th, 2011 at 12:42 pm

Matt,

I hope I am interpreting your project correctly. If so, here is a solution that might at least get you near your goal. I realized on re-reading your message that I oriented my design opposite to your description (times as row headers and employees as column headers), I apologize for that, but it is a small matter to change.

So with a column of potential in/out times entered as 15 minute increments starting on my sheet with 8:00 (just choosing an ordinary start time but maybe your employees work around the clock) in cell b27, I then placed a formula in cell c27 that would place an X in the cell if that time was within my employee Fred’s range of working times. I named the cells holding his in and out times just for clarity in showing the formula.

=IF(OR(AND(B27>=FredIn1,B27< =FredOut1),AND(B27>=FredIn2,B27< =FredOut2),AND(B27>=FredIn3,B27<=FredOut3)),”X”,””) I copied that formula down the column for Fred which placed a X in each cell referencing a time he was working and left blank other cells. Just for color, I formatted all the cells to have a red font. Then using conditional formatting, I set a rule that if the cell did not contain a blank value, it should have a red fill. I hope I have understood your project and that this is helpful. Veronica

October 19th, 2011 at 2:00 pm

Good morning-

The time is not always edited in the same way. Sometimes it is rounded up and sometimes it is rounded down. For example if the original punch was made at 4:59 or 5:01 it would most likely still be adjusted to 5:00. Please let me know if you have any questions or need additional information. Thank you!

October 20th, 2011 at 8:30 am

Hello (again) Veronica:

Simple question:

In cell A1 “1,3,22,10,5”.

What formula/function I need in A2 if I want to get the number of values in A1 (5) as a number that can be used in A3 with a formula like “A2*5”. (In this example the result would be $25- twenty five dollars)

Thank you in advance for your help.

Saul

October 20th, 2011 at 3:54 pm

Sophia,

Here is something to try, perhaps it will get you where you want to go:

I created a sheet with 7 columns: in, adjusted in, out, adjusted out, difference, adjusted difference, and variation. I entered times for each of the first four. Then I placed this formula in the column called difference: =out-in (I am using words instead of cell references in hopes that it is clearer this way) and in adjusted difference: = adjusted out – adjusted in. (I could have modified that formula to accommodate work time that continues overnight, but that is covered elsewhere in this blog.) In variation, I put this formula: =if(adjusted difference>difference, adjusted difference-difference, difference-adjusted difference). To make the nature of the variation stand out I then applied conditional formatting to the variation cells. I went to Home/Conditional Formatting/New Rule/Use a formula to determine which cells to format. I created 2 rules this way. For one of them the rule says: =adjusted difference>difference and the format is set to fill those cells with a certain color. The other rule says = difference>adjusted difference with those cells formatted a different color.

To carry this further, you could convert this data to a table (Insert/Table) and include a total row in your table (Design tab/Total Row check box/function in total row variation cell set to sum). Then when you filter the Variation column by color, you will see the total of all instances where time was lost or filtering by the other color where time was gained.

Veronica

October 23rd, 2011 at 1:51 pm

Hi, Saul,

You might try this in your cell A2:

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1)

The LEN function returns the number of characters in a string, and the SUBSTITUTE function, in this case, removes the commas.

I hope this is what you are looking for.

Veronica

October 23rd, 2011 at 4:24 pm

Yes, Veronica, that’s exactly what I was looking for! (And I also understood and tried myself and learned the use of the functions mentioned).

Thank you and have a wonderful week.

Saul

October 24th, 2011 at 10:47 am

Hi Veronica, I wonder if you can help me as you seem to be the only person in the entire world who understands Excel correctly. I am trying to make a worksheet for work showing hours etc worked. I have correctly input times worked as Start Time, Start Lunch, End Lunch, End Work, Total Hours Worked (Cells A1 to A5 for Mon, B1-B5 for Tue etc) and I have a total worked for all 7 days at H5. But I am really stuck as follows:

I need to work out the overtime using the total hours worked and subtracting a cell which shows the standard hours an individual is contracted to work. The standard hours can vary from 1 to 50 hours. My problem is correctly setting up a cell that will allow me to input any figure in actual hours, say, 25 to reflect 25 standard hours worked so that I can then set up a final box (H6) to show the overtime involved by subtracting “Standard Hours” from “Total Hours”.

I hope you can help and I hope this request makes sense.

Many thanks – Andy

October 24th, 2011 at 7:39 pm

Andy,

Does the standard number of hours vary by employee, but rarely change for that employee? Would it work to enter each person’s standard number of hours in a cell off to the side of the rest of your data and then name the cell for that employee? If I am picturing your situation correctly so far, you could then calculate Joe’s overtime hours in cell H6 with a formula like this:

=If(Joe

November 7th, 2011 at 9:49 pm

Hi Veronica,

I would like to create an XL spreadsheet that can take in time Automatically, Example below.

A B

1——> Start Time Nov 08 2011 5:01 PM

2——> End Time Nov 09 2011 2:10 AM

The Start Time B1 should be the Time When the XL Spreadsheet has been opened by the Employee, my conditions are: It needs to be an Automatic entry and not a manual one using Ctrl+Shift+; etc.

The End Time B2 should be the Time when the XL Spreadsheet has been Closed by the Employee, my conditions are: It needs to be an Automatic entry and not a manual one using Ctrl+Shift+; etc.

Thanks,

Nancy

November 11th, 2011 at 10:22 am

Hi Veronica,

In A1, I have date and time: dd.mm.yyyy hh:mm

In B1, I have a latter date that A1 in similar custom format

Please, what formula should I use for calculating time elapsed from moment A1 to B1, excluding weekend days (Saturday and Sunday), just 8 hours workdays weekly, in a result format like this:

Result (from A1…till B1) : x “years”, y”month”, z “days”, n “hours” and m “minutes”

Of course if x or y, or z, or n=0, don’t display it, display just what is different than 0

Blessings,

Tomasso

November 13th, 2011 at 3:13 pm

Nancy,

To make these entries automatic, you could create macros to do the entry. The Start Time could be accomplished with an Auto_Open macro. Just record a macro named Auto_Open, store it in the workbook, and record the placement of the =now() function in the cell. Capturing the End Time is slightly more complicated. You could first have the Now function in place in a cell so you can copy and paste the value returned by the function. You don’t want to copy and paste the function but the value returned by it when you close the file. Then record a macro (I called my macro CopyPasteValue) that copies that value and pastes it in the cell where you want to display it. Then type this code in the window you get when you right click ThisWorkbook in the Project-VBAProject panel:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call CopyPasteValue

End Sub

I hope this helps.

Veronica

November 13th, 2011 at 9:14 pm

Tomasso,

Here is a monster formula you can try. I did not carry out the test for 0 values in each time unit, but you can see how instances of 0 values can be eliminated in the places where I did.

=IF(DATEDIF(A1,B1,”y”)=0,””,DATEDIF(A1,B1,”y”)&” years, “)&IF(DATEDIF(A1,B1,”ym”)=0,””,DATEDIF(A1,B1,”ym”)&” months “)&IF(DATEDIF(A1,B1,”md”)=0,””,DATEDIF(A1,B1,”md”)&” days “)&INT(IF((B1-INT(B1))>(A1-INT(A1)),(B1-INT(B1))-(A1-INT(A1)),((B1-INT(B1))+1)-(A1-INT(A1)))*24)&” hours and”&MINUTE(IF((B1-INT(B1))>(A1-INT(A1)),(B1-INT(B1))-(A1-INT(A1)),((B1-INT(B1))+1)-(A1-INT(A1))))&” minutes”

Regards,

Veronica

November 14th, 2011 at 8:23 am

Many thanks Veronica. I’ll try this… monster formula.

Blessings !

November 14th, 2011 at 9:06 am

…don’t works… my excel 2007 give me an error…. I didn’t know, that I have to be a kind of PhD (sorry-joke) to make a calculation like this … From internet I find out that DateDiff (with 2f ?!) is not available in Excel 2007- just vba only…

Blessings, TN

November 14th, 2011 at 9:19 am

Tomasso,

Excel does not document datedif; you can’t find it using Insert Function. Nor will you find anything on it in the installed help file. You can find information about it online though, and it should work for you in Excel. In a formula as complex as that one, it is easy to have a typo or missing element that would cause problems. Keep trying.

Veronica

November 14th, 2011 at 9:42 am

Maybe the cause is that function Datedif, works just with date, not date&time ?!!!

“The DATEDIF function computes the difference between two dates in a variety of different intervals, such as the number of years, months, or days between the dates. This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000. For some reason, Microsoft has decided not to document this function in any other versions. DATEDIF is treated as the drunk cousin of the Formula family….Excel knows it lives a happy and useful life, but will not speak of it in polite conversation. Do not confuse the DATEDIF worksheet function with the DateDiff VBA function.”

Seems my doubt about 2f seems to be clearified….

But I’m still wondering…. seems to be difficult….to perform a “simple” computing date&time difference…

Blessings, TN

November 14th, 2011 at 9:50 am

Tomasso,

I find that trying to calculate with both dates and times in the same cell can get complex. The portion of the formula that uses B1-INT(B1) is pulling the time part out of the date/time value, and that gets really clunky to deal with each time you want to work with just the time units. You might try dealing with the difference in dates in one cell and dealing the time differences in the neighboring cell to tame the monster a bit.

I agree it seems more complex than anyone would like for it to be.

Veronica

November 14th, 2011 at 10:18 am

Ya… initially I tried out with a banal example in A1 cell, like 13.10.2011 15:57 and 13.10.2011 17:32 for B1 cell.

With a simple =B1-A1, my result is 00.01.1900 01:35 pretty strange isn’it ?!!!. The time calculation it’s ok (01:35), but for date,… 00 days ok, 01 for month ? and…1900 for years …!!!.

Yes, I know perfectly the GIGO principle….. but with first G, from garbage not from good…!!!(smile !)

I’ll try to split my excel date&time columns (A and B) into 4 colums: A in A (first_date) B (first_time), then B into C (second_date) and D (second_time).

The motto seems to be: why to be easy, when is easier to be …difficult !!! (SMILE !)

Blessings, TN

November 15th, 2011 at 11:49 pm

Thanks for the tips Veronica. You’re a genius! I have a question regarding elapsed time, schedules and calculating hours scheduled. Is there a way to sum hours worked on a schedule even when some shifts are “on call’? For example, if A1:G1 return time elapsed, but C1 is an “on call” shift on a schedule, written as text, how can I sum hours worked in F1? I can’t seem to figure out how to write a formula to intuitively ignore the non-time results. Thanks for the help!

November 16th, 2011 at 10:07 am

Craig,

I am trying to replicate your scenario to sure I understand the problem and the goal. I have entered times in cells A1 thru E1, but cell C1 has the text “on call” after it. So the cell entry looks like this: 2:00 on call

All of those cells plus cell F1 where I put my formula are formatted with the custom [h]:mm format. When I place the function =sum(a1:e1) in cell F1, it totals just the values in the cells with no text. Is that what you are trying to do? Or are you also wanting to capture the time component of the on call cell?

Veronica

November 16th, 2011 at 7:11 pm

Hi Veronica,

This may seem a funny response, but I have thoroughly confused myself in the last 24 hours and changed my spreadsheet so many times I have no idea what it used to say I am actually using cell B1 for “In Time”, C1 for Out Time and D1 for Scheduled Hours. I tally all 7 “Scheduled Hours” cells for each day of the week in cell Y4. The formula in Y4 reads:

=SUMIF(D4,”>0″)+SUMIF(G4,”>0″)+SUMIF(J4,”>0″)+SUMIF(M4,”>0″)+SUMIF(P4,”>0″)+SUMIF(S4,”>0″)+SUMIF(V4,”>0″)

That was a silly way of getting around summing non-contiguous cells which I’m sure is really, really ugly.

Can you tell me what the difference is between these two formulas please when it comes to time elapsed in excel:

First Example =(C4-B4)+IF(C4B15,C15-B15,(C15+1)-B15)

The first is yours. The second is something I found online before I found you. I assumed yours is simply a nested use that is simpler and more elegant than the second example, but they produce different results when multiple cells times are summed…

Also, and this may be a silly question, why is the “1” in each of the above formulae not a “12”?

November 16th, 2011 at 7:17 pm

Sorry, should say:

First Example:

=(U4-T4)+IF(U4B15,C15-B15,(C15+1)-B15)

November 16th, 2011 at 10:57 pm

Craig,

I have tried to build a spreadsheet like the one you describe with essentially 3 columns for each work day: Time In, Time Out and Scheduled Hours. If I start in cell B1 and label 3 columns for each day of the week including Saturday and Sunday, I am ready to calculate some totals in column W. This makes me think that I still do not have a good picture of your data. But with that caveat, in the design I describe here, I can place in cell W1 a formula like this that is summing all the values in the “Scheduled Hours” cells:

=sum(d1,g1,j1,m1,p1,s1,v1)

When you are calculating with times, Excel will never return a negative value for time difference. It would show a ####### result instead. So all of the values you are adding should already be equal to 0 or greater.

In the function =if(C1>B1,C1-B1,(C1+1)-B1) it is basically saying “check to see if the value in C1 (end time) is larger meaning later in the day than B1 (start time). If it is, then just subtract start time from end time. If end time isn’t larger, that means the end time was on the day after the start time – as can happen when working a late night shift. To represent a time on the next day we add 1 to the end time because the value of one day in Excel is 1.

I hope some of this is helpful. Sorry if I am not grasping the set up of your data.

I will be away from this blog for a few days upcoming, so if you have other questions, I will be back in touch when I return.

Veronica

November 17th, 2011 at 1:43 pm

Hi Veronica,

With all the questions, I was sure I would find the answer to mine. How do I convert the Hours:Minutes format to hours and tenths (or hundredths) of an hour. For example Clock In at 8:30 AM, Clock Out at 1:45 PM,

gives 5:15 or 5 hours and 15 minutes. I know this is the decimal equivalent of 5.25 hours, but don’t know how to convert it in Excel. Thanks!

November 28th, 2011 at 10:24 am

Dee,

To convert the time from hours and minutes (e.g. 5:15) to hours and tenths or hundredths of an hour (e.g. 5.25), multiply the value of the time by 24 and set the cell’s format to number. I entered 5:15 in cell A1 and placed this formula in cell B1: =A1 * 24

Then I set the format for cell B1 to number. The result in cell B1 is 5.25.

Veronica

November 28th, 2011 at 12:44 pm

NOTE: The question above and the following response have been interpreted at least in part using Microsoft Translator which may impact the translation.

Hola,

Si se pone la fecha y la hora de entrada en la celda A1 y la fecha y la hora de salida en la celda B1, se puede colocar esta fórmula en la celda C1:

= B1-A1

Formatee la celda C1 con un formato personalizado de [h]: mm si anticipas una respuesta en horas y minutos típicamente.

Espero que esta fórmula sirva a sus necesidades.

Veronica

November 29th, 2011 at 6:38 pm

Hello Veronica,

I receive a feed for call records that defaults the call duration to MM:SS as a general format however when I try to format this into a time format so that I can use it to make calculations it inevitably is formatted into HH:MM so where the call originally was 30 seconds expressed originally as 00:30 the call now appears to be 30 minutes. Another issue I’ve run into is it tries to express the duration as time so a 24 minute call in this case is converted to a 24 hour call and then shown as 12:00 am. Is there a way to convert this correctly so that I could run a formula to determine the count of calls within a giving threshold of seconds? i.e. calls greater than 30 seconds?

Thanks,

Sean

November 30th, 2011 at 8:55 am

Sean,

Excel expects to see hours expressed with the minutes and seconds. So it is necessary to include a leading zero to satisfy that component. Thirty seconds will be entered as 0:00:30. Here is a good article on this very issue:

http://answers.yahoo.com/question/index?qid=20071220074009AAA3Uow

Veronica

November 30th, 2011 at 2:29 pm

Thanks Veronica, I hadn’t thought about just appending “00:” to the data. That works great!

December 16th, 2011 at 4:52 pm

Veronica,

Wow …. Totally impressed with the excel solutions. My problem is probably answered in all the comments to date, but I’m not smart enough to harvest what is there.

I’m trying to automate a timesheet that has start stop times. In this case I’m being asked to track (in this order): Travel start/stop, a morning start, lunch start, lunch stop, afternoon stop, evening start, evening stop.

Daily totals should add exclude lunch time and weekly totals on the x axis (right side) should subtotal: total time traveled, worked in day, total lunch hours, and total evening hours

A grand total should add the travel/work hours less lunch hours for the week.

Below are the formulas I used I know (I need to reformat the total fields):

For daily totals: =IF(ISBLANK(D4),IF(ISBLANK(D11),D9-(D8-D7)-D6,(D11-D10)+D9-(D8-D7)-D6),(D11-D10)+D9-(D8-D7)-D6)+(D5-D4)

Travel totals: =(B5-B4)+(C5-C4)+(D5-D4)+(E5-E4)+(F5-F4)+(G5-G4)+(H5-H4)

Lunch totals: =(B8-B7)+(C8-C7)+(D8-D7)+(E8-E7)+(F8-F7)+(G8-G7)+(H8-H7)

Afternoon totals: =(B9-(B8-B7)-B6) + (C9-(C8-C7)-C6) + (D9-(D8-D7)-D6) + (E9-(E8-E7)-E6) + (F9-(F8-F7)-F6) + (G9-(G8-G7)-G6) + (H9-(H8-H7)-H6)

Evening totals: =(B11-B10)+(C11-C10)+(D11-D10)+(E11-E10)+(F11-F10)+(G11-G10)+(H11-H10)

December 16th, 2011 at 5:24 pm

This format may help you understand the odd time card

Start|Stop Times Mon Tues Wed Thurs Fri Total Hours

Travel Time Start 8:00 17:00

Travel Time Stop 10:24 19:24 4:48

Morning Start Time: 11:00 9:00 8:30 8:30 9:00

Lunch Start Time : 12:30 13:00 12:00

Lunch Stop Time : 13:30 14:00 14:00 4:00

Afternoon Stop Time 18:00 17:18 14:00 18:00 17:00 10:18

Evening Start Time 20:00

Evening Stop Time 22:00 2:00

Total Hours : 9:24 9:18 4:30 7:30 10:24 17:06

December 19th, 2011 at 2:31 pm

Jeff,

If I am picturing your spreadsheet correctly, it looks like you have things in good order to accomplish what you need. However, when you are totaling hours and minutes, be sure to set the format in the total cell to the custom format of [h]:mm. Without the square brackets around the h, Excel will only display in the cell the number of hours over a full day’s worth of hours. For instance, in the cell where you show 17:06, your actual total would be 41:06 and the correct format would make it display as such.

Veronica

January 6th, 2012 at 1:04 pm

Hi Veronica,

I have made table for calculating hours and it is working pretty fine. But, I want to extract night time e.g. night hours during specific START and END time. And there is no need to make difference between weekdays and weekends.

C6 is START TIME formatted as [h]:mm,

E6 is END TIME formatted as [h]:mm,

F6 is TOTAL TIME formatted as [h]:mm with formula =SUM(E6-C6+(C6>E6)*1) to calculate times passing midnight.

So my wish is to establish precise time in format [h]:mm in column C6 and E6 so that night time would be extract in column H.

It is a question of calculating pilot’s flight time and, in this case, night “flight” time which has higher pay scale.

I can’t establish formula so that excel recognize night part during particular flight. It is not night shift only “flying during night”.

Thanks

January 6th, 2012 at 1:39 pm

A question for you…what hours qualify for ‘night’? That will play into the formula.

Veronica

January 6th, 2012 at 1:46 pm

Sorry about that. Let’s say for test I will put from 22:00 to 05:00.

But it will be changed for every month due to pilot’s calculation of night.Thanks

January 6th, 2012 at 1:48 pm

If You want I can send You sheet so You will be able to see what I am talking about.

Explanation, I am running a small airline and pilots has higher pay scale during night hours. Why ? sometimes they take-off during night and in the same flight they are landing during day and vice versa.

Thank You in advance

January 10th, 2012 at 6:49 pm

Hi Veronica,

I’ve been working on a spreadsheet to calculate time for workers that have come in to do work on my building. I want to be able to compare the hours they actually work to the hours they say their employees worked at my building. I have the formula of =IF(AND(K80,L80),IF(L8<K8,L8+1-K8,L8-K8),"") and that works great to calculate how many hours a person worked for the day. The problem I'm having is I might have 3 workers come in and work a certain number of hours for the day and need to get a total for all those workers. So, like on Monday I have 3 workers come and they each worked 1 hour and 30 minutes. How can I modify my original formula to multiply by the number of workers?

Thanks

January 11th, 2012 at 9:35 am

Pointman787,NightStart),1-NightStart,[insert here next nested IF])NightStart,E11NightEnd),(1-C11)+(NightEnd),[insert here next nested IF])NightEnd, E13NightStart),1-NightStart,IF(AND(C13>=NightStart,E13>NightStart,E13<1),E13-C13,IF(AND(C13>NightStart,E13< NightEnd),(E13+1)-C13,IF(AND(C13NightStart,E13 NightEnd),(1-C13)+(NightEnd),IF(AND(C13>NightStart,C13< 1,E13NightEnd,E13< NightStart),(NightEnd+1)-NightStart,0))))))))
This seems to work for me, but check it carefully to see if I have interpreted your situation correctly and to be sure that I have copied my own work correctly in this message!
Veronica

I tried to break down all of your possible scenarios for work that would happen either partially or completely during night hours. I came up with the list below, but you might know of others. Below each stated scenario, I have put an IF function to handle that scenario but without the “else” portion which, when I put them together becomes the next nested IF. I also created two named cells – one named NightStart to hold the time that night pay goes into effect and one named NightEnd to hold the time that night pay period ends. Where you see the value of 1 in an argument, it stands for midnight. Each of these statements has a different row number because I was testing each scenario in a different row.

1) Start is before night but end is in night before midnight

=IF(AND(C6

2) Start and end are in night and before midnight

IF(AND(C7>NightStart,E7<1),E7-C7,[insert here next nested IF])

3) Start and end are in night but start is before midnight and end is after midnight

IF(AND(C8>NightStart, E8

7) Start and end are in night; start is before midnight and end is after midnight

IF(AND(C12>NightStart, C12<1,E12

January 11th, 2012 at 9:38 am

Pointman:

Smiley should be item “8.”

Veronica

January 11th, 2012 at 9:46 am

Priscilla,

I am confused by the AND [And(K80,L80) function in your formula and how it fits with the logical test of the IF function in which it is nested. But if all workers work the same length of time, can you just add a column and label it ‘number of workers’? Then in a third column, you could multiply the cell holding your initial calculation by the cell holding the number of workers.

If it seems like I’m missing part of the story, I probably am. Please send more information if I have oversimplified things.

Veronica

January 11th, 2012 at 10:37 am

To be honest not sure how it fits either but it calculates the time correctly lol. Is there a way to add to the formula that last function so I only have one column calculating time? I have another column next to my total hours column showing the running total and not sure how to work in a third column for adding.

C8 has number of workers, D8 has start time, E8 has end time, F8 calculates hours worked.

3 workers, start at 12:10pm, end at 2:00pm, total hours worked 1:50, running total 1:50

1 worker, start 8:45am, end at 10:00am, total hours worked 1:15, running total 3:05

2 workers, start at 8:45am, end at 2:37pm, total hours worked 5:52, running total 8:57

January 11th, 2012 at 12:44 pm

Priscilla,

If your work hours calculation is giving you the result for length of time worked, and you already have a cell in that row where number of workers is entered, just modify your hours calculation formula to add a set of parentheses to surround the existing formula like this:

=c8*(existing formula here)

That assumes that the number of workers formula is in cell c8 in that row. The cell holding your time calculation should be formatted with the custom format [h]:mm.

Then just use the sum function to add all of the hours in that column, but again be sure to use the [h]:mm format for that cell.

January 11th, 2012 at 2:19 pm

I edited the formula and it worked! But it shows an error about inconsistant formula. I can just ignore that, right? I copied the formula all the way down so it will already be there as I keep adding data. Now in the blank lines it shows #VALUE! saying I have an error in value. How can I make it just show blank until I enter more data?

January 11th, 2012 at 3:30 pm

Priscilla,

Yes, the inconsistent formula error is just saying that the formula in that column doesn’t seem to match others around it that perhaps do have some consistency.If the error bothers you, you can right click the cell and choose to ignore the error.

You can get rid of the #VALUE content by including an IF function in your formula like (assuming cells A1 and B1 are cells referenced in the formula: =IF(AND(A1=””,B1=””),””,

then the rest of your formula would go here/em>)In plain English this says, “If A1 and B1 are blank, then the cell should be blank; otherwise show the result of this formula.”

Veronica

January 12th, 2012 at 1:05 pm

That worked perfect! Thank you so much

January 13th, 2012 at 1:46 am

I’m looking for a way to add song durations for building performing sets. Excel always goes to “time of day” instead of elapsed time. I format as m:ss or [m]:ss and type in “3:15” assuming that excel is smart enough to know that since I formatted as minutes/seconds, that my data entry means “3 minutes 15 seconds”. Nope. It thinks I mean 3:15:00 AM. Why can’t this program be fixed to deal with numbers the way people deal with them?

January 13th, 2012 at 9:25 am

Yes, I agree that this can be a frustrating one. There are a couple of approaches you can try. One less convenient one is to include 0 hours in the time you enter: 0:03:15. But typing that extra 0 and colon can be a pain in the neck. Another thing you could do is to enter the minutes in one column, enter the seconds in the next column and use a formula to combine them in a third column. In this example I would have entered 3 in cell A1 and 45 in cell B1. In cell C1 I put this formula:

=(INT(A1)*60+MOD(A1,1)*100+B1)/86400

Cell C1 is formatted as mm:ss and shows a result of 03:45.

Veronica

January 27th, 2012 at 9:57 pm

I have fields with the date and time and need to know the elapsed time.

Ex

a1 = January 1, 2012 3:00pm

b1 = January 3, 2012 5:00pm

Total should be 50 hours

How do I automate that?

Thanks

January 28th, 2012 at 8:26 pm

I am trying to calculate a timesheet with cell A1 being time in at 3:30pm and time out in A2 as 12:30am. When I try to imput 12:30am I get an error that the out time is greater than the in time. How do I overcome this error?

January 30th, 2012 at 9:54 am

Hi, Tamesha,

Here are some things to check. Be sure you enter your times with the AM or PM or enter them as times on the 24 hour clock.The value of 3:30 pm is 15:30 and the value of 12:30 am is 0:30.

Then use this formula in the result cell:

=IF(B1>A1, B1-A1, (B1+1)-A1)

In the case of your example, Excel will do the (B1+1)-A1 calculation because 12:30 AM is on the next day from the IN time. Adding 1 to its value makes that so.

Veronica

January 30th, 2012 at 10:13 am

Mike,

Excel gives a value to the date and time and uses that to perform a simple subtraction that you enter. So, behind the scenes, Excel knows 1/1/2012 3:00 pm as 40909.625 and 1/3/2012 5:00 pm as 40911.70833. In each case the decimal portion represents the time of day.

In your result cell put the formula: =B1-A1 (assuming the later date/time is in cell B1.)

Be sure the result cell is formatted with the Custom format of [h]:mm

Veronica

January 30th, 2012 at 10:24 am

I just used a1 and b1 as an example, its really in cells Q156 for in time and R156 so I’m not sure if the 1’s in your calculation refer to the cells or the value 1. I have tried plugging in the formula you provided and it still will not let me enter into the cell, giving me the “time out is greater than the time in” error.

January 30th, 2012 at 10:34 am

Tamesha,

Given the cells you are using where Q156 holds the IN time and R156 holds the OUT time, the formula would be

=IF(R156>Q156,R156-Q156,(R156+1)-Q156)

I am surprised at the error you are getting. Does it actually use that wording?

Veronica

January 30th, 2012 at 12:00 pm

The exact wording “Out time is greater than in time.” The cells are formatted [h]:mm. The in time I am using is 5:00 pm and the out time is 12:00 am.

January 30th, 2012 at 3:29 pm

Tamesha,

In what cell is your formula? What exactly is your formula?

Veronica

January 30th, 2012 at 7:19 pm

My formula is in the result cell S155. I was using for the result cell =(R155-q155). I changed it to the formula you suggested and it still will not let me enter a value into the time out cell.

February 15th, 2012 at 1:52 am

Hi Veronica,

I know how to write the formula to find out the time difference.

But now my doubt is in cell A1 8:10 AM

and in cell B1 12:40 PM

and in cell C1 the formula would be =B1-A1. and for which the result is 4:30 (4 hours 30 minutes)

But i want 4 hours in cell C1 and 30 minutes in cell D1.

So how to write a formula for separating hours and minutes…kindly help..

February 15th, 2012 at 8:34 am

Hi,

One way to get the result you want is to use two formulas in two columns. In column C you could enter:

=HOUR(B1-A1)

That returns just the hour portion of the formula result in that cell. Then in column D you could enter:

=MINUTE(B1-A1)

That returns just the minute portion of the formula result in that cell.

Veronica

February 16th, 2012 at 8:39 am

Hi Veronica,

Thanks a ton!! It really helped me but am not able to apply the formula on the seconds columns if it is crossing 60.

If it is beyond 60 it should get converted into hour and the same should be added to hours column.

February 16th, 2012 at 8:50 am

Can you give me an example of the type of problem you are describing?

Veronica

February 19th, 2012 at 5:49 am

Hi Veronica,

Hopefully you can help me with a simple? query.

I have a Timesheet with Start Time ~ Finish Time & Elapsed Time [=B1-A1]

eg. 7:00 ~ 16:0 = 10.0 hrs

I wish to deduct a 20 min meal break from the Elapsed Time (10.0 – 0.4 = 9.6hrs)

Is it possible to do this in excel?

Many Thanks in advance.

February 20th, 2012 at 5:21 am

Hi Veronica,

Example:

A1 B1 C1 D1

3hrs 25mins 2hrs 45mins

E1 F1

Total of the above is 5 hrs 70mins.

Now 70 mins should get added to 5hours as it is crossing 60mins

so now i want the result as 6hrs 10mins. For this i applied the formula = E1+ROUND((F1/60),1)

February 20th, 2012 at 7:17 am

Veronica,

I applied your formula to obtain the result of hours and minutes.

But if my working hours are going to next day ie 5:00 PM(current day) to 12:30 AM(next day) then the same formula is not working

And i need to derive the formula for hours and minutes separately for the above timings. Since the over time differs worker to worker.

I tried to use the above formula by using IF condition but failed.

Please help…

Parvathi

February 20th, 2012 at 10:39 am

If you enter the times in this example as:

A1 B1 C1 D1

3:00 0:25 2:00 0:45

with each of those cells formatted with [h]:mm custom format, you can sum them in cell E1 to get the correct result of 6:10. Cell E1 should also have the [h]:mm format.

Veronica

February 20th, 2012 at 10:51 am

Whenever you are subtracting times that have the possibility of occurring on two different days you need to use the IF function to handle that. Given a start time in Cell A1 and a stop time in Cell B1, in Cell C1 you would type:

=IF(B1>A1, B1-A1, (B1+1)-A1)

This function handles both types of situations – start and stop on the same day or start on one day and stop on the next.

Veronica

February 20th, 2012 at 10:53 am

Hi Veronica,

STIME ETIME H M STIME ETIME H M STIME ETIME H M

1/21/2012 8:00 AM 1:00 PM 5 0 1:45 PM 5:00 PM 3 15 5:10 PM 9:10 PM 4 0

1/22/2012 8:00 AM 1:00 PM 5 0 1:45 PM 5:00 PM 3 15 5:10 PM 9:10 PM 4 0

1/23/2012 8:00 AM 1:00 PM 5 0 1:45 PM 5:00 PM 3 15 5:10 PM 9:10 PM 4 0

1/24/2012 8:00 AM 1:00 PM 5 0 1:45 PM 5:00 PM 3 15 5:10 PM 9:10 PM 4 0

1/25/2012 8:00 AM 1:00 PM 5 0 1:45 PM 5:00 PM 3 15 5:10 PM 9:10 PM 4 0

1/26/2012 8:00 AM 1:00 PM 5 0 1:45 PM 5:00 PM 3 15 5:10 PM 12::30 AM

In the above example i have 3 sessions, and to obtain hours and minutes i applied the formula for all sessions individually HOUR(B1-A1) and MINUTE(B1-A1). And the format for these cells is GENERAL

so i got the result as 5 hours 0 minutes…..

But for the last row last session the time starts at 5:10PM and ends at 12:30AM (the next day). The same formula is not working here in this case.

February 20th, 2012 at 11:04 am

First of all, are all of your start times on the same day as the stop times? If so, your formula will work fine. But if you have anyone working a night shift, you would need to use the IF function to allow for stop times on the next day.

But to subtract the 20 minute meal break you could just calculate hours worked (for this example say that is in cell C1) and in another cell on the sheet (say J1) place the length of lunch break – 0:20). Then in the next cell D1 put this formula: =C1-J1 with the cell format [h]:mm.

With this construction, if the lunch break ever changes, you just change the length of time in cell J1.

Veronica

February 20th, 2012 at 11:29 am

Hi

Yes all start times are on the same day.. and the work timings are 8am-5pm including lunch time of 45mins.

After 5pm if the workers are working then it is OT and they will continue the work till next day like 12:30 AM or 2:30 AM as in the above example.

As i was applying the formula of HOUR(B1-A1), to obtain hours but if the worker is continuing the work for next day then am not able to get the formula. So which formula i need to apply so that my hours and minutes will be displayed in individual cells.

I can send the excel sheet for more clarification…

February 20th, 2012 at 12:08 pm

Can i apply this IF(B1>A1, B1-A1, (B1+1)-A1) formula to get the result as hours and minutes in separate cells.

February 20th, 2012 at 12:39 pm

Yes, just wrap the IF function inside the HOUR function like this:

=HOUR(IF(B1>A1, B1-A1, (B1+1)-A1))

And then do the same thing for minutes but enclose the IF function inside of the MINUTE function:

=MINUTE(IF(B1>A1, B1-A1, (B1+1)-A1))

Veronica

February 21st, 2012 at 12:19 am

Hi Veronica

The above formula has worked. I was trying to apply the HOURS after the IF condition.

Couldnt think for a moment that i should apply before the IF condition.

Now it worked perfect!! Thank you so much

Parvathi

February 21st, 2012 at 4:15 pm

Veronica,

I am a nurse and I work for a surgery department in a hospital and I’m trying to compare amounts of time in Excel. I have merged 2 reports into one Excel spreadsheet. One column shows the average amount of time a certain surgical procedure takes (format is custom h:mm) and the other shows the amount of time our scheduling systems sets these times to be on our surgical schedule (format is general). I need to know if there’s greater than 4 minutes difference between the 2 so that I can go in and adjust the times in the scheduling system. I’m struggling to come up with a formula and/or the correct format to find what I need. Here’s an example from 3 different surgical procedures:

Actual System

0:33 40

0:25 26

1:15 85

Thanks!

February 21st, 2012 at 4:41 pm

Suzy,

You should format both the Actual time and the System time with the Custom format of [h]:mm. This format indicates that the entry is an amount of time, not a time of day.

Does it sometimes happen that the Actual is more than the System? If so, the formula that will fit whether Actual is longer or system is longer is this:

=IF(B2>A2, B2-A2, A2-B2)

That result cell should also be formatted with the [h]:mm format. This formula will tell the difference between the two amounts of time no matter which is the longer.

I hope this helps!

Veronica

February 23rd, 2012 at 10:38 am

When I change the format in the System time amounts column to Custom h:mm, all values now display as 0:00. When I click on one of the cells, the “formula” line at the top shows 2/24/1900 12:00:00 AM.

??

February 24th, 2012 at 9:47 am

Hi Veronica,

I hope you can help. I’m trying to calculate onshore and offshore shift patterns with a 5.5 hour time difference. For example if I input a time of 9am into my source cell for India and minus 5.5 hours in my target cell for UK then this shows a value of 02:30 which is correct. However if I input a time into my source cell of 03:00 then my target cell shows and error as this is the previous day. I don’t want to show dates but just the time so that we understand what a change in shift time for one country means to a change in shift time to the other by changing only the time in a single cell.

February 24th, 2012 at 3:52 pm

Suzy,

The custom format needs to have the square brackets around the h as in: [h]:mm. There is a custom format in the list as [h]:mm:ss. Just select that one and modify it to remove the :ss.

Are both lengths of time in any row entered as 0:xx with both cells formatted [h]:mm? If you just enter either of the times as a number like 40, Excel will not interpret that as 40 minutes. It would have to be entered as 0:40.

I hope this helps.

Veronica

February 24th, 2012 at 4:58 pm

When 40 is entered as 0:40, it works perfectly. Do you know if a quick way to convert my system times from 40 to 0:40? (I have 596 lines of data and I really don’t want to have to do that manually if I can help it!)

Thanks!

February 27th, 2012 at 11:06 am

I figured it out… (divide 40 by 1440 and format to Custom h:mm).

Thanks for your help

February 27th, 2012 at 4:00 pm

That’s the way to do it!

Veronica

February 27th, 2012 at 4:09 pm

David,

Could you give more of a description of what you have in each cell as far as formats and formulas so I can get a better picture of what you are trying to do?

Thanks,

Veronica

February 29th, 2012 at 3:44 pm

Hi Veronica,

I hope you can help. I am an assistant at a small machine shop. I am tring to use excel to calculate the Finish Date for each job. I have the following columns: Set-Up time, Cycle time, Load time, QA time, these columns have the custom format of [h]:mm:ss. Is this format correct? for the total estimate time what would be the formula and format?, for the Start date I input it manually, the format is [$-409]m/d/yy h:mm AM/PM;@. What formula and format would I use for the Finished Date? I have the quantity amount on the first cell on the worksheet.

Thanks

February 29th, 2012 at 7:40 pm

Hi Veronica, great teachings above, I really appreciate it

My problem is that within Excel, I am trying to convert date/time stamps (that are imported from another program) in which the time stamp is as follows: 8/10/2011 8:31:36 AM into just the time. However the time needs to be 8 minutes and 31 secs and 36 msecs. Im hoping there is a formula/format out there that can help a I trying to run ANOVAs based on times for a procedure. Thanks a ton!

Avery

March 2nd, 2012 at 11:25 am

Hello,

I tried to set up a spreadsheet putting in some lengths of time for each of the steps in your process, so I am doing a lot of guessing here as to what those lengths might logically be. I had today’s date and time in cell A2 and times for each step in cells B2:E2. I used the same formats you have. In cell F2 I totaled the steps: =sum(B2:E2). Then in cell G2 I put this formula:

=WORKDAY(A2,INT(F2,INT(F2/”8:00″)+(MOD(F2/”8:00″,1)*8/24))

Everything after the ‘A2,’ is just converting the total hours in the process to days, hours and minutes. Then the WORKDAY function adds that amount of time onto the start date in A2.

I hope this works for you.

Veronica

March 2nd, 2012 at 11:37 am

Avery,

Here is a post by Mr. Excel that speaks to your situation:

http://www.mrexcel.com/forum/showthread.php?t=16655

I hope this is helpful.

Veronica

March 13th, 2012 at 6:05 am

Hi,

I am trying to set up a spreadsheet to show elapsed times between two points, either within the same day, or over a period of days. I only want weekdays which hours are 08:00-16:10. I have had some success with datedif and also networkdays both using seperate columns for date and times.

For example:-

work starts at 10:00 on March 3rd and is completed at 15:00 March 7th how long did the task take to complete? I’ve read some of your replies above where you mention square brackets, might this help, and also might want to be brave to include statutory holidays.

Si

March 14th, 2012 at 1:55 pm

Si,

You might try this. I entered start dates/times in column D; end dates/times in column E. In column F I put this formula to calculate the whole workdays between start and end:

=IF(MINUTE(D2)>0.333,NETWORKDAYS(D2,E2),(NETWORKDAYS(D2,E2)-1))

This checks to see if the start time is after 8:00 or not, i.e. was the start day a full day or were there just minutes of that day we should count.

In column G I put this formula to find the simple difference between start and end but consider just the time portion of that result:

=MOD((E2-D2),1)

That result cell is formatted [h]:mm

Try this with a sample of your data to see if it gives what you are looking for.

Veronica

March 15th, 2012 at 2:48 am

Thanks Veronica, I’ll try your formula out and let you know.

Si

March 15th, 2012 at 3:04 am

Hi Veronica,

Perfect, your formula works perfectly, I’ve tried it out with various times and dates, and it gives the exact results I am looking for-I think I even understand the logic behind it.

I’ve been trying to figure this out for days, and could get no help here at work, so I am glad I discovered your blog on this website. Thanks Veronica, you’ve brightened up this cold grey English morning.

Si

March 23rd, 2012 at 9:15 am

Hi Veronica,

I am trying to create a formula for comp time if more than 40 hours. For example: if I have 45 hours in column A1, I want column B1 to be 40 hours, and column C1 is 5 hours x 1.5 . I used the custom [h]:mm format for column A1 (it’s total of the whole week). Please help, thanks Veronica!

Kim

March 26th, 2012 at 9:06 am

Kim,

If you have 45:00 in A1 and 40:00 in B1 (with both formatted as [h]:mm), you could put this formula in C1 (also in [h]:mm):

=if(a1>b1,(a1-b1)*1.5,0)

I am assuming in that formula that there will be instances where the week’s total in A1 will be less than 40 and in those cases the overtime hours should be set to 0.

I hope this serves your needs.

Veronica

March 26th, 2012 at 2:14 pm

Hi Veronica,

Thank you for taking the time to help me. I can’t wait to try this formula as soon I am done with the current project. Hope you have a wonderful day!

KIm

April 11th, 2012 at 6:19 am

Hi Veroncia,

i am facing difficulty in using time subtract.

I want to subtract End time 17:15.33 from start time 10:05.17 to get answer of 7:10:16.

Thanks

April 13th, 2012 at 7:45 am

Qasim,

You should just put the start time in a cell (A1 in my example) and the end time in another cell (B1). Then in the result cell (I will use C1) put this formula: =B1-A1. You will want the result cell’s format to be the custom format [h]:mm:ss.

In your message I notice you have your start time displayed with a decimal point instead of a colon between the minutes and seconds. If you used that same form in your spreadsheet, that could be causing a problem for you.

Veronica

April 16th, 2012 at 10:26 am

Hi Veronica,

When I put a formula in a cell, (ex: =SUM(D5:D20) ), it just won’t calculate, just gave me $0.00. I tried to change to general, text, clear the cell, but it still didn’t work. Please help. Thanks,

Kim

April 16th, 2012 at 12:29 pm

Kim,

When you say you tried to change the format to general, text, etc., are you referring to the result cell where the formula is sitting? Or are you checking also the formats of the cells where your values are sitting (D5:D20)?

Veronica

April 16th, 2012 at 3:31 pm

Hi Veronica,

I click on the cell where I put the formula (the result) and changed to text or general. I read somewhere that if I change like that, later I can just go back and put the formula and it will work , not sure if it’s true or not . The format for others (D5:D20) are currency.

Kim

April 17th, 2012 at 11:01 am

Kim,

Typically Excel will automatically change the result cell format to match that of the source cells which is what I think one would usually want – currency adding up to currency. Even if you set the result cell format to General or Text, it should still display the correct result. If the source cells were in text format, that would be a problem.

But from what you describe I can’t see any reason why Excel would not add those entries. The only thing I would consider next is whether some other settings are in place impacting the cell values. Perhaps you inherited this file from someone else? Or the cells have previously been used in some other way without clearing all content?

Veronica

April 27th, 2012 at 4:29 am

how do u calculate min and hr spend on a job example

A1 -16 min

A2 -35 min

A3 – 1hr 20 min

And then in A4 calculate all time spent togettor on all

April 30th, 2012 at 8:25 am

Philip,

When you enter the times that are only minutes, such as 16 minutes, you still need to enter the hours as 0 in this way:

0:16 or 0:16:00 (if you want to indicate seconds). So if your times are entered as 0:16:00, 0:35:00, and 1:20:00, your total in cell D1 (=sum(A1:C1) becomes 2:11:00. Cells should have the custom format of [h]:mm or [h]:mm:ss – again depending on whether you intend to capture and calculate seconds.

Veronica

May 16th, 2012 at 9:21 pm

I have a beginning date-time entered in Cell1 and an ending date-time entered in Cell2, both entered as m-d-yyyy h:mm. I have been able to calculate the elapsed hours and minutes between the two date-times in a third cell (Cell3) with the formula Cell2-Cell1 and with Cell3 formatted as [h]:mm;@. Now I want to concatenate the hh:mm value I see in Cell3 into a text string… but when the string appears the elapsed value hh:mm value I see in Cell3 changes to a wrong value! I tried the TEXT() function in the concatenation but could not make it work. How do I get the correct elapsed hh:mm value in Cell3 into a text string?

May 17th, 2012 at 8:18 am

Problem solved – Text() function works: “stringA”&TEXT(Cell3,”[h]:mm;@”)&”stringB” gives correct elapsed hours:minutes in concatenation. Thanks anyway for being a great resource.

May 21st, 2012 at 11:09 pm

How do i minus these two time cells.

1:59:57 and -0:24:54

Thanks,

May 22nd, 2012 at 1:13 am

i mean subtract these 2. I think this is simple question. Any suggestion will be appreciated. 😉

May 22nd, 2012 at 6:11 am

I am working on time calculation form 24hours period & over 24 hours with 4shifts as (A(morning), B(afternoon, C(night) & G(general)) with shift times of 8hours with break of 0:30minutes. See below shift timings:

A Shift: 6:30AM to 14:30PM

B Shift: 14:30PM to 22:30PM

C Shift: 22:30PM to 6:30AM

G Shift: 9:30AM to 17:30PM

with this some times resources do extended shift upto 12hrs & more: I was above the calculate the timings very well till 24hrours their by deducing break timings but once the 24hours limit is crossed my formula is not working can any one help me on this: please see below folmulas with start time & end times, shifts; etc

Col I Col J Col K Elasped time Time spent

St time End Time A/B/C/G =IF(J2>I2,J2-I2,(J2+1)-I2) =IF(L2=TIMEVALUE(“24:00”),L2-TIMEVALUE(“2:00”),IF(L2>=TIMEVALUE(“16:00”),L2-TIMEVALUE(“1:30”),IF(L2>=TIMEVALUE(“9:00”),L2-TIMEVALUE(“1:00”),IF(L2>=TIMEVALUE(“8:30”),L2-TIMEVALUE(“0:30″),IF(K2=”A”,IF(AND(I2=D$2),L2-G$2,L2),IF(K2=”B”,IF(AND(I2=D$3),L2-G$3,L2),IF(K2=”C”,IF(AND(I2=D$4+1),L2-G$4,L2),IF(K2=”G”,IF(AND(I2=D$5),L2-G$5,L2),””))))))))

May 22nd, 2012 at 8:27 am

Beku,

I am curious about the minus sign in front of your second time value, -0:24:54. Is is just that the second time value is 24 minutes and 54 seconds? Or are you trying to subtract a negative amount of time? Excel does not deal with negative times.

If you just want to do a simple subtraction of two positive time amounts, you can use =If(A1>B1, A1-B1,(A1+1)-B1). Format the result cell with the [h]:mm:ss format.

Veronica

May 22nd, 2012 at 4:30 pm

Shiva,

Could you expand on your description of what you want this function to do?

May 23rd, 2012 at 6:54 am

Veronica,

Hi, I want this function to calculate total man hours spent on an allocated JOB by deducing break times of 30minutes per single shift respective of the person works in single shifts or continue further to work more than a single shift. The results should be displayed in format of total hours & minutes; thank you.

May 28th, 2012 at 6:41 am

Hi Veronica,

Hope you had received my mail along with sample log, had you able to work around on the same. Please let me know; thank you.

May 29th, 2012 at 8:58 am

Shiva,

I have spent some time trying to analyze your formula for calculating final time. I have to say I do not have suggestions for improving on your work to most efficiently calculate all possibilities.

Veronica

May 30th, 2012 at 2:36 am

Veronica,

Thank you for your try and comment!

Shiva

May 30th, 2012 at 11:33 pm

Veronica,

I am really excited I found this site. I have a problem. I am a board game designer that is using excel as a game helper – in this case basketball. I am trying to create a clock on the spreadsheet that starts at 12:00 and counts down to 0:00 in increments of either 20 or 10 second subtractions. I used a control button for each of the two increments and then started with 720 and each time the button subtracted the increment I used a nesting function to display the required time 11:40, 11:30, etc BUT BUT BUT I ran into the 64 nesting limin AAAARRRGGHHH and now I am stuck. So, how would I do this? You would be a life saver

May 31st, 2012 at 8:27 am

Jeremy,

You are right the limit for nested if statements in Excel 2010 is 64. An alternate approach to getting what you want is to use a macro. Even if you are not well acquainted with using macros and the VBA editor, I think you will find the instructions at this web site very helpful:

http://www.ehow.com/how_8610357_insert-countdown-timer-excel-worksheet.html

Using their code, I set my cell E1 = to 0:12:00 and formatted it with the custom [h]:mm:ss format. When I run the macro the countdown begins.

I hope this gives you what you need. Good luck with your game development!

Veronica

May 31st, 2012 at 9:28 am

Hi Veronica,

That was very helpful for a different project I am working on and will certainly use it. However, for this one in particular the clock is just a representation of a clock – for a card based basketball game. So, instead of actually counting down seconds from 12:00 to 0:00 the user needs to be able to click a button to subtract time from 12:00 in :20 or :10 second increments by the click of a button ( used command buttons for this) until they get to 0:00 that’s why I used the nesting function. So, I am guessing that is some sort of formula where I start with 12:00 and create a formula using a Macro and VBA (just started learning this) to subtract :20 and :10 by choice with a button. So, it really is a number of game “turns” being counted but represented by a “clock” because it is a sports game. Thank you so much you are very generous with your time – this site has been awesome for learning and seeing what others do.

Thanks,

Jeremy

June 9th, 2012 at 9:25 pm

I am having trouble with coming up with an excel formula that will total the number of hours worked per shift and automatically subtract 45 minutes for a meal break. I know I could do this with b1-a1+d1-c1 but due to the page size available to me it would be easier if I could do finish time less start time minus 45minutes or 60 minutes for example. So something that would resemble b1-a1- 45 minutes to show my results I am using cell formatting of Customer with [h]:mm

June 11th, 2012 at 7:36 am

Lynn,

I placed the standard meal break time in a cell off to the side of my data. I called that cell “lunch” but you wouldn’t have to name it. I then subtracted start time from end time, as in B1-A1 and then subtracted the value in the cell called lunch:

=(B1-A1)-lunch

I made this formula a little more simplistic than you would need for employees who work a night shift. If you have that situation you would have to expand the basic calculation to:

=if(B1>A1, B1-A1, (B1+1)-A1)-lunch

Veronica

June 11th, 2012 at 8:14 pm

Thank you it worked

June 25th, 2012 at 12:13 am

Hi Veronica,

I’m trying to compute for hourly wage. For this example, the ff. are the given:

HOURLY RATE (in $) = 2.00 (in cell A1)

TIME IN- 8:00 (in cell C6)

TIME OUT- 12:00 (in cell D6)

HOURS WORKED- 4:00 (in cell E6)

When I subtracted 12:00 with 8:00 (C6-D6), the result was 4:00 (E6). With this example, the wage should be $8.00 (4 hours x $2.00), but when I multiplied HOURS WORKED with HOURLY RATE (E6*A1), the result was 0.33. How do I get the right amount?

Thanks!

-John

June 25th, 2012 at 12:16 am

**oops! CORRECTION:

D6-C6=E6

June 25th, 2012 at 7:57 am

John,

Your formula for multiplying time worked times pay rate must also include multiplying by 24:

=hours worked * pay rate * 24

Veronica

June 26th, 2012 at 11:38 pm

Got it! Thanks Veronica!

-John

June 26th, 2012 at 11:53 pm

Hi Veronica,

Follow-up question, how do I subtract minutes?

We give a 15min grace period for time in. So in this example:

TIME IN- 8:07

TIME OUT- 12:00

How do I subtract the 7 minutes so that it still computes as a full 4 hours of work?

Thanks again in advance!

-John

June 27th, 2012 at 7:48 am

John,

In the solution below I am using the serial number values for the times involved – .33333333 = 8:00 am, .01041667 = 15 minutes. So for the calculation where the start time is in cell A1, the end time is in B1 and the pay rate is in E1, the formula would be:

=(B1-IF(Value(A1)-0.3333333<0.01041667,0.3333333,A1))*E1*24

The Value function converts the time entered in cell A1 to its serial number value for comparison.

This formula checks whether the difference between the value in A1 and 8:00 (0.3333333) is less than 15 minutes. If so, it uses the value for 8:00 to subtract from B1’s value. If not, it uses the actual value in A1 in the subtraction.

Veronica

June 27th, 2012 at 8:54 pm

It’s my first time to use the Value function. Thanks Veronica.

-John

July 2nd, 2012 at 5:22 am

Veronica, Hi,

I am looking for a solution to mark/highlight ‘Duplicate’ text i a column using IF statement they may be list of many of 1000= names listed. Can you help me to sort this. thank you.

July 2nd, 2012 at 7:49 am

Hi, Shiva,

There is an excellent article on highlighting duplicates at:

http://spreadsheets.about.com/od/excelformatting/qt/090516_find_duplicates_in_Excel.htm

I hope you find it helpful!

Veronica

July 4th, 2012 at 8:16 am

Thank you for solving a long standing issue I had with our schedules when inputting out times that went past midnight.Educational institues are always 1 of the three elements in research of development of techology.Your contribution and other similar institues are proven source of information.We at Cellocean acknowledge and appreciate and always as supporting plateform to publish information.

September 7th, 2012 at 7:59 am

Hi, Veronica,

Can you help me on the issue of “too many cell formats” pop up message with in excel 2007, were i cant loose the existing data collected through the pervious months in analysis, Thank you for your help!

September 7th, 2012 at 2:19 pm

Shiva,

Try this article from Microsoft Support. It may address the problem you are experiencing.

http://support.microsoft.com/kb/213904

Veronica

October 8th, 2012 at 9:04 am

Worked for me too. Thanks Veronica.

November 13th, 2012 at 7:00 am

Hi Veronica,

Im having trouble calculating the days, hours and seconds between two dates.

Can include only hours from 8:00 am to 5:00 pm (to maximum of 9 hours per day)

Exclude weekends and holidays

Like 1/31/2012 1:10:40 PM to 2/8/2012 4:35:59 PM

Can you help me to sort this. thank you.

Rick

November 13th, 2012 at 12:14 pm

Hi, Rick,

One less complicated way to work through this would be to first use the NETWORKDAYS function to let Excel automatically eliminate the weekends and holidays for you. Of course you have to supply the list of holidays. So in my example my start date and time are in cell A2, the ending info is in cell B2, and the holidays are listed in the range I2:I10. In cell D2 I placed the function: =NETWORKDAYS(A2,B2,I2:I10). That result just gives me the whole number of workdays. Then in cell E2 I calculated the difference in hours, minutes and seconds between the times in the two cells:

=IF(MOD(B2,1)>MOD(A2,1),MOD(B2,1)-MOD(A2,1),MOD(B2,1)+1 – MOD(A2,1)) and formatted that cell with the custom [h]:mm:ss format.

Here are links to other articles about this type of calculation:

http://www.excelforum.com/excel-general/671326-removing-non-business-days-and-hours.html

http://excel.bigresource.com/Difference-between-dates-and-times-in-days-hours-mins-working-hours–h6gozWXS.html

http://www.pcreview.co.uk/forums/years-months-days-hours-minutes-seconds-formula-t2813668.html

Veronica

November 13th, 2012 at 4:32 pm

Hi Veronica,

Thanks for your help, apreciated.

Find Duplicates in Excel without using Conditional Formatting.

One way to find duplicates on colum A using IF(,,):

Sort by colum A with “Headers”

On colum B “Sequency”

=IF(A2A1,1,B1+1)

To identify Duplicate on colum C “Duplicates ID”

=IF(B2>1,”Duplicate”,IF(A3A2,””,”Duplicate”))

To keep the last one and delete others

On Column D “Duplicates Count”

=IF(A3A2,B2,0)

Copy & Paste as values (Column B and D)

Delete column B

Sort by colum C

Delete values = 0 on colum C

Rick

November 13th, 2012 at 4:36 pm

Correction

On column b “Sequency”

=IF(A2A1,1,B1+1)

December 10th, 2012 at 11:19 am

Veronica,

I am trying to figure out how to automatically calculate the average time elapsed between each time a compactor is emptied. I want to take all the dates a unit was emptied and calculate the average amount of time that has elapsed for all these dates. I’m trying to make it into a user friendly form that anyone can use just by typing in the date and having it automatically calculate the average time elapsed for management to use. Can you help me?

December 12th, 2012 at 11:09 am

Melody,

One approach to getting the values you want would be to set up your data in three columns with one holding the Compactor Emptied Date (in my example column A), one calculating Elapsed Time between that date and the previous date (my column B), and one calculating the Average of Time Between Empties (my column C).

In column B, I placed the formula: =A3-A2 [This would be the formula in cell B3 when the most recent date has just been entered in cell A3.]

In column C, I placed these nested functions: =AVERAGE($B$3:OFFSET(C3,0,-1)) [Making cell B3 an absolute reference using the dollar signs means that as this is copied or extended down the rest of the column, cell B3 will always be the beginning of the range considered in the average. The OFFSET function makes the end of the range always the cell in column B in the row currently added.]

By making the entire range of data a Table, you cause the calculations to automatically extend to new rows as new dates are added in column A. You will probably need to set formats for columns B and C to get the display you want.The user just needs to click the cell in the next row at the bottom of the existing data, type in the date, and press ENTER or TAB.

I hope this helps you get the result you are looking for.

Veronica

December 18th, 2012 at 7:22 pm

I’ve read through all the posts and couldn’t find one that answered my specific question (I did try several of the formulas but they did not do what I wanted).

Is it possible to creat a timesheet in Excel for Time In, Time Out (lunch), Time In (lunch), Time Out using 12-hour format rather than 24-hour format? I have one that is currently working with staff entering their time in 24-hour format, but when I change the format to 12-hour, it doesn’t calculate their time properly.

Thank you!

Tina

December 19th, 2012 at 11:11 am

Tina,

But when they enter times in 12 hour format they are also entering AM or PM, right?

Could you send me some sample data?

Veronica

December 19th, 2012 at 12:25 pm

Veronica,

Oh sorry….no. They only enter the time. So they enter: IN 8:00 OUT 1:30 IN 2:30 OUT 5:00.

When they enter IN 8:00 and any time up to 12:59 OUT, IN 1:59, OUT 5:00 it calculates correctly. Once they enter an OUT that is 1:00 or later, it does not calculate.

I have the cells formated as [H]:mm.

The reason I am trying to make it work this way is because, beginning in January, they will also be utilizing a web-based time clock that shows time in 12 hour format. I’d like them to simply be able to transfer the time to their Excel timesheet without having to worry about changing to 24 hour format or adding AM/PM.

Tina

December 19th, 2012 at 12:41 pm

Tina,

Your problem probably has to do with the fact that if you enter a time in a cell without indicating AM or PM, Excel assumes AM. So the person who enters 8:00 for IN and 1:00 for OUT would seem to have arrived at 8:00 AM and left at 1:00 AM. Excel wouldn’t know how to calculate the difference between the OUT time and the IN time in that case.

Veronica

December 19th, 2012 at 8:02 pm

Veronica,

Thank you! Sounds like the only way to do it is to have the users enter AM/PM when entering their time. I’m sure they can get used to that.

Thanks again,

Tina

January 7th, 2013 at 1:26 pm

Hi Veronica, Happy New Year.

Not sure if this is the right place to ask the question, but i am logging the amount of time a team spends on a task. The teams operate around the clock.

We log the start datetime & end datetime of the task however this can go from the end of one day and into the next.

Because we are logging as datetime there is no problem calculating duration, the problem is reporting activity per day.

Because the task can begin before midnight and end after midnight I am trying to work out how I report what time portion of the task was carried out on a specific day, I am trying to do this in a pivottable with a calculated field. Any suggestions? Thanks!

January 8th, 2013 at 8:42 am

Guy,

Here is one solution. I set up a sheet such that two rows serve each work time with one row pulling the date of the

shift’s START and calculating the hours worked on that date, and the next row pulling the date of the shift’s END

calculating the hours worked on that date. Even if all work is on the same date that’s OK.

I set up 5 columns: A = Start date and time, B = End date and time, C = Total hours of shift, D = just date

portion of start information in the first row for the shift, and E = calculation of hours worked on the date the

shift began. On the second row for the shift I entered in column D a formula to pull just the date portion of the

shift’s end and in column E I calculated the hours worked on that date.

So for the example of a shift with a start date and time of 2/2/12 20:00 and an end date and time of 2/3/12 4:00, I

set it up like this:

2/2/12 20:00 2/3/2012 4:00 8:00 2/2/2012 4:00

2/3/2012 4:00

The formula to pull just the date from the date and time is =TRUNC(A2) given the date and time in A2 and the format

of the cell set to Date. The formula to calculate the hours worked on the start day given the start date and time

in cell A2 and the end date and time in B2 is:

=IF(DAY(A2)=DAY(B2),B2-A2,1-(A2-TRUNC(A2)))

The formula to calculate the hours worked on the end day again assuming the start date and time in cell A2 and the

end date and time in B2 is:

=IF(DAY(A2)=DAY(B2),0,B2-TRUNC(B2))

With these calculations in place you can create a pivot table with the Date field as the Row Label field and the

Hours (column E) as the Value field – choosing to show the sum of hours.

I hope there is something here that you can use in your solution.

Happy New Year to you!!

Veronica

January 15th, 2013 at 3:26 pm

I may have missed it in your previous posts but, I need to calculate months elapsed between two dates that are formatted as: yyyymmdd

i.e. How many months between 20061015 and 20120801?

Thank you for any help you can provide!

January 16th, 2013 at 12:57 pm

Maureen,

In my example I have the more recent date in cell B1 and the earlier date in cell A1 so the formula would be:

=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)

The cell holding the formula needs to be formatted as a number.

Veronica

January 16th, 2013 at 3:55 pm

I had tried that but, it returns a ‘#NUM!’ error. It seems confused by the year coming first?

January 16th, 2013 at 4:03 pm

Maureen,

Sorry, I forgot to address that in my first response. If you are indeed entering the dates as you show above: 20061015, Excel is not recognizing them as dates. You need to put / or – between the year, month and day. Otherwise Excel thinks you are using the serial number for a date that is further out in the future than Excel’s limit.

For instance the serial for today, Jan. 16,2013, is only 41290.

It is OK that the elements are in the order you have them. They just need the separation symbols.

Veronica

January 28th, 2013 at 6:45 pm

Hi Veronica, I am interested in capturing patient waiting time, in minutes. Is there a way to have excel enter a timestamp in column B1 with the current time if a external barcode is scannced into column A1? I would like to track patient wait times with as few keystrokes as posible. This would be repeated once the service is provided to generate the time difference.

I believe I can format the cells (formula) as needed once the data is captured to equal wait time in minutes. Is this possible?

Riley

January 29th, 2013 at 5:38 pm

Riley,

You might find this article on VBA for causing an action when a cell’s content changes helpful:

http://www.ozgrid.com/VBA/run-macros-change.htm

Veronica

June 17th, 2013 at 7:35 pm

“Hi Andy,

This is tough because while Excel has the NETWORKDAYS function that will calculate whole days between two dates, I know of no function that breaks that down into hours and minutes. Here’s an approach you might play with. It is many steps so maybe a macro could help. It seems you need to capture for each part of the calculation, the value (date and time) of day’s beginning and day’s end for each date in your range. If the employee began work at 9:30 on Jan. 16, 2011 and that date is sitting in cell A1, in another cell you could use one of the Rounding functions to calculate just the date portion of the starting information. Then if you add to that number the serial value for 17:00, quitting time that day, you have the start time and end time for that day which can be subtracted to see the number of hours and minutes worked that day. You could do the same process for the end date only you would be using the value for 8:00 am added to the date portion of the serial value of the end date. Once you have the amounts for each day, you can total them.

Does anyone else out there have another approach?”

This is the exact problem I need to get resolved as well… I used the NETWORKDAY to get a sum of hours but I still don’t understand your method of limiting 8 hours for the work days.

Please help!! Thank you.

June 20th, 2013 at 5:42 pm

i want know how to a specific time including how many times from a time list.

for example:if we get a team which start on 10:20, we can see that team engaged with 3 teams.those are

which team is enter in 10:10,10:15 and 10:20

start end

team 10:05 10:15

team 10:10 10:25

team 10:15 10:40

team 10:20 10:30

team 10:25 10:40

team 10:30 10:55

team 10:35 10:45

team 10:40 10:55

team 10:45 11:10

team 10:50 11:05

i try to solve this problem,but i cannot. if you can please build a logical test for count in excel

July 21st, 2013 at 4:31 am

Hi,

I would like to know how to compute how many hours a man works if he had answered 55 calls and 16 emails in 8 hours…So for 55 calls how many hours did he spent and for the 16 emails how many hours did he spent which total to 8 hours.

Thanks,

Lloyd

July 24th, 2013 at 7:13 pm

Veronica,

Thanks for this guide! It took a bit of playing around, but it works!

Hopefully Microsoft added something to make this easier in Excel 13.

Thanks again,

Josh

September 4th, 2013 at 9:20 am

If I am picking up a load of product based on the production per hour, for an example, a factory produce 754 gallons of juice per hour and a full load is 7540 that meant it will take the factory 10 hours to creat a load. The factory close between 2:00 am and 4:00 and again between 14:00 and 16:00. Is there a way that excel could help calculate the down time in pick up schedule sheet ?

September 9th, 2013 at 3:18 am

need help!

why do i get a “FALSE” result with this formula?

=IF(AND(F1TIMEVALUE(“0:30:00″)),”RV”,IF(AND(F1TIMEVALUE(“0:51:00″)),”IE”,IF(F1=TIMEVALUE(“0:00:00″),”MV”)))

value of my D1 is 11:30 AM

value of my E1 is 12:20 PM

F1 is a FORMULA =E1-D1 and gives me an answer of 0:50:00, however, instead of giving me a result of “RV” it shows “FALSE” instead..

September 29th, 2013 at 7:34 am

Veronica,

I am needing a formula which will automatically advance a set number of seconds onto the previous time.

I need it to show in minutes, seconds, and tenth seconds.

If my start is at 00:00.0, I need increments of 2.5 seconds. So next should show 00:02.5, next 00:05.0, next 00:07.5, next 00:10.0 ……..etc

Please help me, thank you very much

September 29th, 2013 at 7:38 am

addendum: I would like to drag this formula down the same column and it automatically applies this formula, adding the 2.5 seconds to each cell. The 2.5 seconds is an example, I will need to change this value from time to time, but it will always include the tenth of a second.

October 17th, 2013 at 10:29 am

Angel-

Sorry for the lateness of this response, but there seems to be a problem with your IF Statement. The way your IF statement is structured, it is doing what you have asked; none of the conditions are true, so you are getting a “FALSE” response. (Did you mean for the first condition to read >=.30?)

The AND is used when you want multiple conditions to be true simultaneously so not sure what you are wanting to do here. The way this is set up, the AND is really not necessary since the values of .30, .51, and .00 cannot be true at the same time for the same cell.

In this instance, the answer you are looking for should be FALSE since .50 is not listed as one of the “TRUE” conditions. The statement could read like this (the AND is not necessary):

=IF(F1=TIMEVALUE(“0:30:00″),”RV”,IF(F1=TIMEVALUE(“0:51:00″),”IE”,IF(F1=TIMEVALUE(“0:00:00″),”MV”,”FALSE”)))

Again, I am not sure if you mean to use >= or just =. However, by just using the = sign, you will get an “RV” response if F1 contains the value of .30.00 or an “IE” response if F1 contains the value of .51. If none of these 3 responses are true, you will get a “FALSE” response – or whatever you specify at the end of the nested IF function.

Hope this helps.

October 17th, 2013 at 11:41 am

Dave,

Set a cell with your increment using this time format: mm:ss.0.

Format the result column with this same time format.

All you have to do is start with a value (in this case 00:00.0) and multiply by the increment value.

In cell B2 the formula would read: =B1+$A$1. Using the drag fill handle in lower right corner, drag the handle down as far as you want and the values will increment. If you want to change the increment, all you have to do is change it in cell A1.

A B

00.02.5 00:00.0

00:02.5

00:05.0

00:07.5

October 17th, 2013 at 11:45 am

Dave,

Sorry – when I submitted my response, the columns squished together; Cell A1 has the increment value, and the 2nd column should look like this (with formula starting in B2):

00:00.0

00.02.5

00.05.0

00.07.5

November 13th, 2013 at 4:50 pm

I’m not very familiar with Excel functions. Can you help me determine the formula for adding 15 business days to a cell that contains a date? Also, what would the fomula need to be if I want the destination cell to be blank if the date cell is blank?

November 20th, 2013 at 12:49 pm

How to add 5:30 PM of first day to 5:30 PM of next days.

November 21st, 2013 at 3:31 pm

Pam,

Regarding adding 15 business days to a date, try this formula:

=IF(A4=””,””,WORKDAY(A4,15,H1:H2))

where cells H1 and H2 are listing the holidays that are non-working days.

December 18th, 2013 at 4:21 am

Veronica,

Hi, I was looking to count number of <04:31:00, =00:00:00 hours spent in a month with below formula but the same is not working within pivot table when columns are inserted. Please can you suggest any solution?

=COUNTIF(B2:OFFSET(L2,0,-1),"=00:00:00")+(COUNTIFS(B2:OFFSET(L2,0,-1),"00:00:00″))*0.5

Thank you, Shiva

January 9th, 2014 at 12:36 pm

Veronica PLEASE HELP:

I created a table below and my supervisor would like for me to “add a metric on how many days are under 1:00 vs Over 1:00 as compared to the total number of days (as a percentage)”. Could you please assist me with this?

Date Max Delay

10/1/2013 0:00:41

10/2/2013 0:00:51

10/3/2013 0:00:23

10/4/2013 0:00:54

10/7/2013 0:02:04

10/8/2013 0:01:02

10/9/2013 0:00:12

10/10/2013 0:00:51

10/11/2013 0:00:14

10/14/2013 0:00:25

10/15/2013 0:02:30

January 21st, 2014 at 6:04 am

Hello there. I need a little help as Im doing a quiz in excel and I would like to count time passed for each answer.Example: when somebody enter answer in B1, clock should start counting time in C1 from 00:00 (min:sec), and when it enter another answer in B2, time should be displayed in C2 how much it passed from time in C1 till the time pressed ENTER on C2. Same thing for answer in B3: in C3 should be difference between time in C2 and moment of presing ENTER (for an answer) in B3. So, I would need data how much time person spend on each question. Than at the end, total time spent on all questions. I hope I have made it clear, since english is not my native, I am trying to be specific as I can.

I hope there is a solution for my problem and looking forward for an answer.

Regards abd best whishes from Croatia!

January 24th, 2014 at 4:01 am

Hi Veronica

i am trying to make a sheet which required time mission like 20 of them but need to finish first one first than go to 2nd one out of 10 different items and its run for 24 hours when first mission finish 2nd start and goes on till 24th and i have 24 hours and 10 factories to make that happen on time value of each mission increase on each mission i never finish them on time so want to know if i can make a table which can calculate what should i make so i can finish stuff on time. 10 items are different value and build in various time.

February 10th, 2014 at 9:02 am

Hi, I need formula to calculate time once a number is copied from a cell.

July 6th, 2014 at 12:36 am

I have read everything on this site but must have missed the answer to this question.

Using the Australian custom of date 23/4/09 and 24 hour time, What is the time difference in Day,Hour, Minute between the following

3/7/14 08:15 and 5/7/14 16:20.

Can you please explain the steps to get the result.

Thanks

John

September 30th, 2014 at 4:14 am

Hi Veronica,

I have been searching a lot recently for time based formulas as i need to calculate processing time excluding weekends. Your article above is the best that I have come across explaining everything in simple terms. Well done.

Regards

Khalid

October 26th, 2014 at 7:33 pm

Hi, I am not really familiar with exel.

I am interested to convert spent time in hours that can be calculated as a normal number ( 37:30:00 to convert in 37,5 hour).

Thank you very much.

November 3rd, 2014 at 7:41 pm

Hi

I’m trying to work out if I have a value that represents ‘total number processed’ example ‘2000’. I know that it takes 15 minutes to process an application, how do I work out total time taken in a time format to process all 2000?

Been playing around with different formulas, can’t make it work!

Thank you

Ben

November 26th, 2014 at 11:47 am

Hi,

What is the average response time and display rate in excel?

Thanks.