Free Microsoft Excel 2013 Quick Reference

- How can one zero out or drop the seconds in a time format?
- How do i calculate a work schedule in excel?
- Imported Date & Time format with calcs. managed in excel from imrp
- How do I create a formula to calculate pace per mile?
- Creating a Timesheet/Calculating Time
- Auto Place : in Time Data Entry while Protected
- Averages with dates and times
- Calculating Time Based on Time Per Item
- Calculating Times: Minutes and Hours
- End - start less one hour for lunch
- How do i calculate hours worked in excel?
- How do I calculate minutes when given two times?
- Excel needs hh:mm where hh>24 for hours worked per week
- Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins?
- Timesheet: Calculate Overtime Rates crossing into next day
- Tricky hour calculation (re overtime spreadsheet).
- Time sheet calculation when lunch period used for appt.
- Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins?
- Calculating an average but excluding boxes with text
- Calculating salary (Hourly paycheck)

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

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!!!

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.

THE QUESTION:

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

5:04:30pm"

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.

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'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:

HOURS MINUTES 4 24 12 5 18 36 -------------------- 34 65 <--- Totalling columns as general numbers 35 5 <--- The result I want to displayI'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?!

~MGarcia1234

(mgarcia1234@hotmail.com)

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.

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!

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

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.

example

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

charles

http://www.mvps.org/dmcritchie/excel/datetime.htm 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)

really...

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...

Michelle

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

time09:00,1,0)))

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!

Skip4t4

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>

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

week.

----------------

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.

http://www.microsoft.com/office/comm...et.f unctions

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

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):

=IF(D2<>"",SUM(D2-TIME(7,30,0)),"")

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!

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.

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

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

Km

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.

TY!