Free Microsoft Excel 2013 Quick Reference

How to calculate the hours and minutes in excel Results

Hello - 1st off, I just want to say that this forum has been a great source of information when dealing with excel help - GREAT JOB.

I have been having an issue I just can not seem to get to work correctly. I am not even sure if I am going about it the right way or if I am just missing one little detail.

Over all picture, I am trying to create a time sheet that will automatically import the time stamp activity: Shift Start, Lunch Start, Lunch End, & Shift End from an updated pivot table spreadsheet. The information in the pivot table is text only data.

I have been able to figure out how to import the time stamp from the pivot table + converting from text to time format. The problem I am having is, I want to drop the seconds off the time so it is just hh:mm. I have tried changing the cell format to time hh:mm format, but when I start doing the calculations/converting to decimal to figure out how many hours worked, it still figuring the seconds (that are not showing in hh:mm cell format).

The sample I uploaded contains the time sheet I have created with a small sample of the pivot table.

fyi: the pivot table is structured so only the design can be changed, but none of the data.

Basically I just want to the hours and minutes to be either imported over or have the seconds dropped/zeroed out before or during the conversion/calculations.

Can someone please help me out and see if there is anyway this can be achieved?

I am sure you guys can figure it out in 5 minutes or less. I am know just enough to know I do not know enough about excel

Thank You

I am so close to completing a scheduling worksheet, but have a problem with
one of the formulas. I have created dropdowns for 'in' and 'out', using AM &
PM for the times, a formula to calculate the daily number of hours and a
formula to calculate the total number of hours scheduled for an entire week.
My problem is that I want to deduct lunch periods from the daily schedule,
but have been unable to figure out that calculation. I want to be able to
deduct :30, :45, and :60 minute lunches. At this point, I would even settle
for the ability to deduct a fixed time period of :30 minutes. Any suggestions
are very much welcome!!!

Sorry for lengthy question but need to master this topic - thanks for time
also I need to do ongoing date work so i wan to know the best way ongoing not
a quicky solution if possible.

I will make use of any external sites or resources you point me tward.


I have reports coming out of cognos imromptu that have the following format
in the interval or time field - I need to use case created and case closed
(impromptu) allot but these are also general excel date time format questions:

In impromptu the date field is formatted like "000 00:00:00.000"
day hr
min sec etc.

When I export to excel I get the following in the field and in the formula bar

Create date cell " 1/2/05 17:04" , in formula bar I get "1/2/2005

Close date cell "1/2/2005 20:35" , in formula bar I get "1/2/2005
8:35:10 pm"

The calculated field of close date less the create date from report writer (
impromptu gives the following in excel

cell " 0 3:30" , in formula bar I get "1/0/1900 3:30:40 AM"

Now in Excel if I do a calculation on the two dates I get the following:

cell " 0 3:30 , in formula bar I get "c11-b11"

Something you should know - I can create afield in report writer that will
dump the count of days from 1900 so i get a cleannumber calculation. Then I
can do celan calcs in excel.

However I would like to know how to handle dates and not rely on the days
from 1900 method

? is there a way I should try to format in report writer - limted!

? what is the soundest way to handle dates with time in them so I can get
clean hours and minutes - some time I need to knwo hours been created and
closed , sometimes I need count of days open.

I do need to get clean fields with year, month, and day so I guess zi need
to format so a calc or parse can be done - any thoughts. I would even be
interested in calculating the week of the year.

I work in a 24/7 tech support company - working as an analyst for a point of
reference so the pressure is off on week days & weekend issues but I would
liek to learn about this as well as handeling holidays.

Again thank you for your personal time on this.

I have an Excel spreadsheet to keep track of my training for a race I am going to do. I would love to enter a formula that will calculate my pace per mile. I already have a cell where I enter my distance I ran, a cell where I enter the time it took me to run that distance, and I have a cell to calculate the pace per mile. It is not as simple as dividing the distance by the time taken to run the distance, there is more to it than that, I just don't know what it is :-)

Let's say in cell D2 is my distance of 4 miles, and in cell E2 is my total time of 45 minutes and 29 seconds, what formula would I enter in F2 to calculate my pace per mile? Do I need to format any of the cells? If so, how?

I went to an online website that will calculate my pace for me. Running for 4 miles with a total time of 45 min and 29 seconds gives me a pace of 11:22. If I simply divide 45:29 by 4 miles, I get a pace of 11:32. There are 60 minutes in an hour and 100 parts in a whole. You can't compare the two and that is where the confusion comes in.

Thanks for any help!

I need to create an Excel spreadsheet (2000 ver) where I can enter hours and minutes in separate cells, then have them total up all together as hours and minutes.

I've been given the task of having to calculate all the time I've spent on certain projects, so the number of hours/mins to calculate will change over time as I add/subtract time.

I'll try to illustrate in an example:
   4          24
  12          5
  18          36
  34          65             <--- Totalling columns as general numbers
  35          5             <--- The result I want to display
I've never had to make a formula in a spreadsheet other than "=sum(cell:cell)" so I have no idea how to go about it.

I'm sure for an experienced Excel user, this is a no-brainer. Did I also mention that I suck at math?!


Using Excel 2010 and have a work sheet protected and sent out for others to fill out and send back. We don’t want them to change any of the formats for obvious reasons.
One of the fields requires them to enter hours and minutes (in military format) but they don’t want to key the : and want to just enter 1330 and have Excel put in the :. The resulting time is used in calculating hours worked.

I wrote the macro below but and attached it to the sheet but it won’t run when the sheet is protected.

What should I do / try to get this seemingly simple request working?

Private Sub Worksheet_Change(ByVal Target As Range)
' cancels out if the conditions are not met
If Intersect(Target, Range("B17:B42")) Is Nothing _
Or Target.Count > 1 _
Or Not IsNumeric(Target.Value) _
Or Len(Target.Value) < 3 _
Or IsDate(Target.Value) Then Exit Sub
' force number format to text
Target.NumberFormat = "@"
' parse the value entered to put a colon right before the last 2 digits
Target.Value = Left(Target.Value, Len(Target.Value) - 2) & ":" & Right(Target.Value, 2)
' force Text format to Time
Target.NumberFormat = "h:mm"
End Sub

I didn't know how to search for an already answered question.

I am using a formula to calculate the time between date in and time in, and date out and time out for my company.

For example, the "date in" is column A, "time in" is column B, "date out" is column C, and "time out" is column D. If you are familiar, the formula is to find the difference (time) between is as follows:

=C2-A2-(B2>D2)&" days "&TEXT(1+D2-B2,"hh:mm")

I want to find the average of all the times collectively, but when I try to use the function in Excel, it does not work. I figure because it is in days and time, not just hours or minutes.

Please send suggestions on how to do this! I would really appreciate it!

Hi ~

I'd appreciate any help. I have a spreadsheet that has the number of used books in column A and the number of new books in column B.

We've determined that it takes about 90 seconds to prepare a book to be put on the shelves.

I want to find out how many hours and minutes (don't care about seconds) it would take to process the books.

Here's an emample. I've also attached a spreadsheet so you can see that my formula doesn't seem to work correctly. I would appreciate ANY help since I'm kind of at my wit's end. I'm using Excel 2003.

# Used Books # New Books Hours/Minutes for Used Hours/Minutes for New 3,915 566 The formula I have in here is "=IF(A2=0,"",((A2)*90)/(60*60*24))" The formula I have in here is "=IF(B2=0,"",((B2)*90)/(60*60*24))" 3,703 474 2,076 238

ok my boss got fed up with this. and she gave it to me to do

my question is i need to get a formula that will calucate hours and min . its for how many hours the employee has not worked. some of them would be strait hours some would be just min there is no way to tell.

lates 2 hours
anp(absent no pay) 12 hours
sicks 55.5 hours
no calls
early outs 21 min
(this is just an example if it were real this person would be fired)

i know this adds up to 69.85 hours but i can't fuiger out a way to get it to calucate in excel.. i'm reading excel 2000 by gene weisskopf but have not found anything in there to help me yet.

i know i could have it all changed to min and then devied by 60 to get the hours but how do i get it to read what is mins and whats hours?

any one have any sugestions?

thanks for any help you could provied this newbie


I have gotten some great tips from this forum and have been to the site which was very helpful.
But - can someone give me the quick and painless way to calculate the
difference in time less one hour (lunch).
I am currently using =E9-C9+(C9>E9) and formatted it to [h]:mm:ss , then
having a cell next door to it to calculate it into decimal hours (F9*24-1)
but how do I get that (-1 hour) into the cell that calculates the
hours/minutes? I have tried tagging a -1:00 on the end or just a -1 but I
get the ole #VALUE error.

Thanks in advance - I know this is likely easy...


I want to track hours worked from clock in and out times...but I need to see
hours worked for each hour of the day rounded to the nearest 30 minutes using
the IF function MAYBE? I also need to use the same formula for each hour so
I can easily replicate it.
clock in time 08:20 clock out time 17:20

09:00 result = 0.5 attempted formula = if(clock in time>08:00,if(clock in
10:00 result = 1.0
11:00 result = 1.0
12:00 result = 1.0 etc
18:00 result = 0.5

Clock in and out times will vary and the formula should include reference to
both the clock in and out time +/- 30 minutes.

Help, I am stuck and my formula sucks!

I have a spreadsheet that contains one set of cells that is time in and
another set of cells that is time out.
I have been able to calculate elapsed time in hours and minutes by
converting the time in and time out to military time. Excel is reading this
as a time of day rather than the length of time.
How do I convert the elapsed time into minutes so that I can measure averages>

In order to calculate time worked per week in a time sheet, excel needs to
provide support for hh:mm formats where the hh argument is greater than 24,
for example if I worked 40 hours and 15 minutes in a week, I should be able
to add (8:00+7:30+8:30+8:00+8:15)=40:15. I should be able to calculate
40:00-(time worked so far) to get how much time I still need to work this

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane. unctions

I've been fiddling and fiddling for some time now this evening but I
seem to be missing something completely today. In the past I've dealt
with adding/subtracting times formatted the same way as the end
results, so they've worked from the get-go.

In today's case, I have a spreadsheet that I'm using to calculate best
fits for movies transferred from VHS to DVD via a DVD Recorder. My
DVD Recorder deals with hours and minutes but my internet source gives
total running time in minutes only.

How do I get Excel 2000 to add up a column of minutes and then give
the results in a time formatted as hours and minutes? If I could get
this to work, I could then just plug in different movie running times
finding best fits?

Thanks! I'm in the corner with my dunce cap, I can't figure out my
approach today in a way that works. D


This is my first post in this forum and I've got myself doozy of a problem

I'm busy recreating our overtime claim form and had some success with the calculation of breaks from overall time.
The problem i'm now facing is how to calculate hours on adjusted rate..
I have an idea what to with Min/max to determine a time, I hit a brick wall when a shift starts in normal time, crosses all of ajusted rate, and ends in normal time again.

Attached is a copy of the test sheet - I have tried a few approaches as some of the red values show, and some of the test values I randomly have on the right-hand side outside of the sheet.

The conditions are:

Automatically deduct 30 minutes for every 6hrs+ worked (mandatory break) per time in/out. (working)
Show total hours worked below each day (working)
Calculate adjusted rate for hours worked between 8pm-6am (NOT WORKING)
EDIT: Times must be written in the same day as Start/Finish, allowing for cross from one day to another

Same conditions apply for weekend work


I wouldn't be asking is I hadn't searched google and numerous excel-related sites for an obvious solution. A lot of the formulas used have been based on answers found elsewehere but i have reached an impasse with my current knowledge,
Where possible, I'd like to avoid using VBA,
It really is driving me to distraction, so any help or pointers in the right direction would be appreciated

I realize that if I just put the hours worked myself in the
spreadsheet I just created this week, i.e., did the calculation myself
and just typed in the actual hours worked, that that would avoid the
problem I'm running into. However, it would be just so much easier
and so neat to type in the actual start and end times involved and to
then let Excel figure out what the overtime is if there is indeed
overtime <g>. That would be too kewl for words! <g>

I did up an overtime logger/calculation spreadsheet. It worked fine
as is until today; today I didn't work a full day so the calculations
were useless for this date.

I yielded results on a massive search today on the net. The formula
below is what I was able to come up with and I have this in cell E2
which subtracts 7.5 hours from D2 (corresponding to regular hours
worked so must not be included in overtime calculation):


D2 shows 7h30m (calculated by end time C2 - start time B2 less 30
minutes for lunch) and E2 shows 0h0m because the above formula
subtracts 7 hours, 30 minutes to account for regular work hours vs
overtime, as mentioned above.

Up till here, so far so good.

If I do actually work 7.5 hours, then, I get this 0h0m display.
But if I didn't work 7.5 hours, like today, the above doesn't work.

I had an appointment today and so I actually only worked 4.5 hours.
D5, then, shows 4h33m instead of 7h30m.
But E5 shows #### where it should show -2h57m
(because I didn't work nearly 3 hours due to my appointment).

I've tried displaying the end results differently through cell
formatting, but nothing works.

The goal is to just put the time started in the morning and then my
usual time out and then to put the time I left. So by entering 3
different times only, and without taking out a calculator, when I left
at 6:30 p.m. tonight, the spreadsheet should determine that on this
day I didn't work overtime and that I am still actually 1 hour short
or so.

I know this is tough to understand by description, but hope the
concept itself is understood even if not the above.

So, here is the problem again in a nutshell:

How to get the above #### to display the time correctly when there is
a negative number of hours to show, i.e., in this case -2h57m
yet keep 0h0m when I did work the full day.

(I use custom time display of: h"h"m"m" to get our French Canadian
24 hours display which is easiest for all to understand as that's what
we use in our dept.)

Thanks in advance!

QUESTION: I have a spreadsheet including all of my in and out times for work, leave, and lunch. If I leave in the morning for an appointment that runs through my lunch period, what formula can I use to calculate the time away from the office MINUS the lunch period or said another way, how can I get Excel to only count leave time that does not fall within the range representing my 45 minute lunch time?

I often have medical appointments during the day that I use either sick or annual leave for. I try to make my appointments overlap with my lunch time in order to reduce the amount of leave I must claim. My employer counts time in whole hour increments only, so we must keep our own running totals for partial hours used until it equals a whole hour. I'm able to handle the increments by rounding down to the whole hour interger and adding the remainder to a running total. Other details:

For example I arrive for work at 7:00 AM, leave for an appointment at 10:30 AM (begin sick leave calculation), my lunch start is 11:00 AM and lunch end is 11:45 AM, and I return from my appointment at 1:00 PM. I want B11 (below) to show 1hr 45 min or 1.75 instead of 2 hrs 30 min or 2.50.

-All time cells are formatted with h:mm AM/PM
-"Number hours used" cells are formatted as numbers with 2 decimal places
-"Number hours claimed" cells are formatted as number with no decimal places
-Current formulas in speadsheet are show in brackets [ ]

B2 - Arrival Time [user entry]
B3 - Lunch Start [user entry]
B4 - Lunch End [=SUM(B3+0.0315)]
B5 - Sick Leave (S/L) Start [user entry or empty]
B6 - S/L End [user entry or empty]
B7 - Annual Leave (A/L) [user entry or empty]
B8 - A/L End [user entry or empty]
B9 - Departure [user entry]
B10 - Number hours worked [=SUM((B9-B2)*24)-0.75
B11 - Number S/L hours used ?????
B12 - Number of A/L hours used ?????
B13 - Number of work hours claimed [=INT(B10)-(B14+B15)
B14 - Number S/L hours claimed [=INT (B11)]
B15 - Number A/L hous claimed [=INT(B12)]

I truly appreciate any help. I don't think the answer is difficult, just beyond my mental reach.

I've been fiddling and fiddling for some time now this evening but I
seem to be missing something completely today. In the past I've dealt
with adding/subtracting times formatted the same way as the end
results, so they've worked from the get-go.

In today's case, I have a spreadsheet that I'm using to calculate best
fits for movies transferred from VHS to DVD via a DVD Recorder. My
DVD Recorder deals with hours and minutes but my internet source gives
total running time in minutes only.

How do I get Excel 2000 to add up a column of minutes and then give
the results in a time formatted as hours and minutes? If I could get
this to work, I could then just plug in different movie running times
finding best fits?

Thanks! I'm in the corner with my dunce cap, I can't figure out my
approach today in a way that works. :oD

Hi all,

I'm trying to get excel to calculate the average time spent on appointments by officers in a small team. Each of them sees people each day, and as we're very busy I'm eager to get the time spent on each appointment down, by giving them a target of the number of people to see each day. At the moment we gather some stats on this, which show how many people are being seen by each officer.

The attached example sheet shows for one week a list of columns with officer's initials at the top, and says how many people were seen by each person each day during that week and then a how many people overall for the week.

I'd like to be able to generate statistics on the amount of time each officer has spent on average with customers, and base this on the fact that people have 360 minutes per day (they do 6 hours of appointments and another hour for admin) to see people.

I think the spreadsheet attached gives a good idea of the system and what I want to do.

Any help would be really greatly appreciated!

all the best


Hello =)
i used to have a job that paid a certain salary per day, but sometimes the boss told me to leave after 4 hours, and sometimes after 8... so in order to see how much i make per hour all all i did was make make a "Worked from" and a "Worked untill" columns, Subtracted the "From" from the "Untill" and got the number of hours.

i used to write the times like this "15.75" (for 3:45pm/15:45) and just ignore any extra minutes by rounding up to down. and assuming i worked till 9:45pm, Excel did 21.75 - 15.75 = 6 hours.

Now i have a job that is 24 hours around the clock.
meaning i could work from 9pm, untill 2am. but calculating 2.0 minus 9.0 gives me -7, and no only is it wrong, since 9pm to 2am is 5 hours, it gives me negative hours.

id like for a way/formula to simlpy be able to write the exact time with mins too, Even if it 15:12, without having to convert 30 mins to half etc, and have excel give me the result in how many hours + mins i've worked that day.


No luck finding an answer? You could always try Google.