Excel Magic Trick 501: Excel Time Format & Calculations (10 Examples)
Вставка
- Опубліковано 25 сер 2024
- Download Files:
people.highlin...
Learn about:
1.What time is in Excel
2.Time in Excel is a decimal between 0 and 1, and the decimal represents the proportion of one 24 hour day that has elapsed
3.Time Number Format sits on top of the decimal
4.The decimal is used in time calculation formulas, not the formatted number
5.Payroll time Calculation
6.Day Shift Time Worked Calculation: End Time Start Time
7.Night Shift Time Worked Calculation: MOD function
8.Round Time to the nearest 5 minutes with ROUND or MROUND functions
9.Add Time above 24 hours using custom Number formatting
10.Add Time above 24 hours using formula and the number 24 and General Number Format
To learn how to download workbooks, search for and watch this video title:
excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks
Watch this video:
Excel Magic Trick 286: MOD function & Time Calculations (Time For Night Shift, or Negative Time)
Great Video. All the info you need to know about Excel Time operations in less than 15 minutes. Mike is the best teacher of Excel in the whole world.
Ran into a heap of time calc problems working out a sleep diary/quality of sleep record for my partner, and solved them very quickly using your brilliant video. You are so good at balancing the theory you need to know with the practicality of solving problems. Thanks a million !
Thank you for the awesome instructions! I admire anyone who can clearly get their point across.
I love your videos! They are life savers! Saves me lots of time and makes my work a breeze! i love you! You are so effing brilliant!
BEST videos I've watched in a long time and THANK YOU for helping me resolve an issue with my electronic time card that I email each week. Good stuff man!
I am glad the video helps!
Calculating time is always difficult if you do not how the deal with the format.Therefore the video clarify how calculation should be done. As always an very useful video
Thanks! I am glad that the videos help!
you =mod() trick for the 24 hour time has saved me sooo much time, Great video keep up the great work!
Very good tutorial. I'll be sure to check your videos when I have Excel questions. Thanks!
Glad you liked it!
You are welcome!
Great Work, you're absolutely the Master of excel teaching all around :)
Very good tutorial!! THanks for this
You are welcome!!!
Thanks solved a lot of my problem mate
Arigato
Sunny Chandiramani You are welcome!
Wow, planiolro, you are a really smart Excel guy - was there some new stuff in this video that you learned from, or was it just the comprehensive nature of the video that was of interest to you.
Since I always get so many questions about time, my intention was to make one video that had most of the time tricks so that I could always send people to this one video...
Brilliant !! very clear and did the job .. Keep going :)
kind of confusing but i am happy with this video it helped alot! thanks!! :)
It is a Number Formatting, not a value that you "see". The 0.333333 is what is really in the cell: time is the proportion of one 24 hour day (8/24 = 0.333). To remove the "AM": Format cells dialog box, Number tab, Custom, "Type" text box put" hh:mm:ss
that will give you 08:00:00.
If that does not work it is because you have altered the actual value. To remove all and start over, Home Ribbon, Editing group, Clear, Clear All.
I am interested to study excel your video helps me a lot. but i need a certificate course so kindly help me to join in online course sir
thanks very helpfull
Thanks a lot , so helpful , could you tell if I can freeze tow upper rows and tow left columns together , by freeze pane function , thanks again.
MOD looks useful for 24 hour periods spanning two dates. I combined the date with the time and used an IF formula that added a day to the date if the finish time was less than the start time. This still restricts periods to less than 24 hours but has the advantage of permitting calculations of elapsed time between different days in excess of one day. The result gives the exact period but unfortunately there is the complication of having to discard the time or obtain the date from its source to calculate how many dates have elapsed.
Hi Mike! This is very helpful tutorials, I really appreciate the job you're doing over here.Thank you so much!!!!
But I don't see the EMT494-501 file for downloading
.
Hi there, this is very helpful and I really thank you for all the videos.....
Syed Imtiyaz You are welcome!
I got a problem with my current job here!!!!
I would be greatly thankful to you if you could
help me to overcome it
I actually got an excel sheet which contains timings of patients who are coming in, waiting for the consultation for some time and then leaving the center and I need to prepare a Pivot table and chart which can reflect the time which the patient is waiting for to get treated, etc.
Thank you so much. I have a problem that I can not figure out the calculation and I was wondering if you can help with this. I want to enter a military time, for example, 20:00(entry 1), add 15 minutes to that(entry 2), then add 13 hours(entry 3), and subtract 5.15(5 hours and 15 minutes)(entry 4). Once all of the entries have been added up, I want to find the actual hour in military time.
Hi There,
I have found your videos very useful, I am not sure where else to look but i have an excel issue with calculating time and conditions. I am formatting a spreadsheet for timesheets at work and the conditions are whatever time the staff had worked before 8:00 and after 17:00 on that day are to be added together and this will be his/her extra time accumululated out of normal working hours. Example is, if someone worked from 7:30 to 18:00, I want this to calculate and display 1:30 hours. Hope to hear from you soon. Cheers
your tips and tricks have been a great resource to me over the years. and i thank you.
I have a particular problem and i am unable to find the answer among your myriad of videos.
I would like to add together the following to give me overall hours on the job i.e.
A -Time left last site, B- time arrived on new site, C- time departed from new site and D- the travel time away in minutes.
A 14:00 pm + B 15:00 pm + C 02:00 am + D 120 Minutes = total hours.
Your assistance or if you can point me in the right direction would be greatly appreciated.
Hi Mike, what video do you recommend that will help me calculate rates according to time periods?
Dear sir if i have a same date in four to five cells i want "dddd" only in first cell other downward cells have to be blank is this possible if possible than how
HI i have a question for you. How do you get one tenths of hour every six min. here is any exmaple: 1400 2202 8.1
you every six minuets is one tenth of hour. Right how i have it round up after every one or two minuets.
I am currently working 80+ hours week. No time. Try posting to THE best Excel question site:
mrexcel[dot]com/forum
Is it possible to calculate subtract a certain number of hours from a time to get an earlier time? for example: I would like to calculate what time I should go to bed if I want to get 8 hrs of sleep and I have to wake up at 5:00am. This one seem like it should be easy, but I am stumped so far.
Yes, the essences of this video is the concept that a time like 8:00 AM has a number under the number formatting of 8/24 = 1/3 0.333. So if you have 5:00 Am in cell A1, then to subtract 8 hours, you must use the number 8/24 or 1/3, so the formula is: =A1-1/3. If you do this sort of thing often, it pays to study this, as presented in this video.
@@excelisfun Thank you!
Help! - Excel 2010, trying to build a 24hr schedule in 15min increments and excel will not recognize the pattern and auto populate. very frustrating.
how do i get a get my total hours as actual hours not in decimals. when my hours are added as a total for the week or the day it will come up with a number like 86.97 or 7.97 in decimals. i want the actual hours and minutes i worked. this formula didnt work for me [h]:mm it gives me like 226 number and i know i didnt work that many hours in my week.
Mike, could you please advise how to take out "unique videos" out of 6000+ videos. Kindly assist. Your videos are more than "awesome", awaiting your help on this. My goal is to see all your videos.
I am sorry but I do not understand what you are asking.
how do i add, say 10 minutes to the time that i have now in excel?
Just add 10/(60*24)
u can add any X minutes like this just divide the X minutes by (60*24)
thanks^^
How do I figure the time difference of Pacific time vs Eastern time in real numbers....I have 8:30 PM and 5:30 PM...the result should be +3 because East Coast is 3 hours ahead....I also have 4:30 PM and 5:30 PM for a part of Alaska or the Pacific Islands....they should read -1 or -3....PLEASE HELP. I"m going crazy. How is this possible?!
3 hours is 3/24 = 1/8 = 0.125, so subtract that amount.
"Military time".. meanwhile I'm just European and we all use that time format🤣
You are welcome!
You are welcome!