HomeОбразованиеRelated VideosMore From: Dinesh Kumar Takyar

Calculating time in MS-Excel

834 ratings | 395204 views
Our Excel training videos on YouTube cover formulas, functions and VBA. Useful for beginners as well as advanced learners. New upload every Thursday. For details you can visit our website: http://www.familycomputerclub.com Microsoft Excel makes calculations with 'time' very easy. So if you wish to calculate the monthly payments for your employees you need to have a start time, an end time and the payments per hour. Whether the person works during day-shifts or night-shifts, Excel can help make the calculations easy and automatic. Just ensure that you enter time in the format x:y:z, where x = hours, y=minutes and z=seconds if you wish to have seconds in your data else just use x:y, i. e. hours and minutes separated by ':' (colon). If you want enter time as 'am' or 'pm' just add a 'p' after the time. If you don't write anything after the hours, minutes and seconds MS-Excel assumes it is 'am'. If you use the '24-hour' clock, Excel has no problems with 'am' or 'pm'. Get the book Excel 2016 Power Programming with VBA: http://amzn.to/2kDP35V If you are from India you can get this book here: http://amzn.to/2jzJGqU
Html code for embedding videos on your blog
Text Comments (119)
ajit pgims (3 days ago)
Very nice sir
Java Team (1 month ago)
sanjay talekar (3 months ago)
hindi me bool
Dinesh Kumar Takyar (3 months ago)
yahaan jaa: https://www.youtube.com/user/MSExcelinHindi
Md.Bachchu Mia (5 months ago)
Thank you...........
Jamal Said (6 months ago)
Thinks sar
Soumya Roy (6 months ago)
How can we get negative time with different indication?
Sudhir Rawal (10 months ago)
Sir , my simple question if i had a time slot i.e 18/00 to 00/00 and my night calc from 22/00 - 00/00 so i want to put an value in cell -a1 =18/00 and b1 =00/00 and results will calculated by automatically to c1 =22/00 and d1 =00/00 How could i do plz help
Lynx Team (3 months ago)
the best way not to feel lost with time-slots and calculations is to use time-tracking software like tmetric or harvest or tsheets
MARWAN TAHER (11 months ago)
Shira Marcus (11 months ago)
Thank you for this video. it was very helpful!
Zain Khalid (11 months ago)
Hi, it is a challenge for any excel user, I need to calculate working hours and minutes but I do not want to enter the time in time format. i want to enter 10-16 instead of 10am - 4pm or similar . can you make a formula which converts start time and end time from the same cell and then calculate the time ???????????????????????
Mohanraj Gct (1 year ago)
sir i have an equation for example (overtime amount /overtime hours)=per hours rate. my problem overtime is time format. if i divided my overtime amount, my result wrong. can you help me how fix this problem?
Dinesh Kumar Takyar (1 year ago)
This link will help: https://www.exceltrainingvideos.com/payment-calculations-based-on-timestamps-in-excel/
kapoor5302 (1 year ago)
Hello sir I am beginner in excel.. plz help me to make an excel where a person log-in time is there and it will calculate that what will be his logout time as per CST & IST time zone (since i work at night shift US process) to complete 9 hours in office for the day..... plz help sir vry urgent.....
Damaris Figueroa (1 year ago)
Matt Silva (1 year ago)
How would I use this to add 3 time periods in a day? say 6:30-10:30, 14:00-18:00, & 22:00-2:00. I understand and have used your formula for one time period but cant figure out the rest. I have up to 3 time periods per day and want to make sure it calculates all three appropriately regardless of the time of day like the last one mentioned about (22:00-2:00). This is what I have in place: =IF(D6>E6,1+E6,E6)-D6 , but need to add F to G & H to I. Thank you
Dinesh Kumar Takyar (1 year ago)
Search https://www.exceltrainingvideos.com or http://www.familycomputerclub.com
anju av (1 year ago)
thank you sir
Happy Heart (1 year ago)
I just created the greatest spreadsheet of all time.. lol Thank you
I solved my problem. Thanks a lot.
Dinesh Kumar Takyar (1 year ago)
sagar suri (1 year ago)
sir i have problem in exporting data from sale sheet to invoice copy
Anne Drew (1 year ago)
why is become a 43.75 instead 43.15? thanks for answering
Palash Chakrabarty (1 year ago)
Thank You Mr Kumar i was face problem when i calculation day one to next day morning it come to error now i am solve the issue . but when working continue 2 day how is it work is it if + 2 day
franzel dizon (1 year ago)
This is very helpful! Thank you so much!
NS Farm (1 year ago)
Thank You . you saved my day
Mon Mi (1 year ago)
Great video, thank you!!!!
DHARMA MOHAN (1 year ago)
Thank you
Shabs M (1 year ago)
thank u so much.....so easy to understand
Gina Davis (2 years ago)
This was an amazing video. Simple instructions and easy to understand. It was exactly what I was looking for. THANK YOU SO MUCH!!!!
Lawson Bushu (2 years ago)
vaibhav more (2 years ago)
how to count total month hr count ps help
Jagan Nathan (2 years ago)
I want millisecond format and I don't have hh:mm:ss.000 in my excel 2013 format window. Is there any add-in for that? pls help.
Loreen Maravilla (2 years ago)
Thank you!
pavan kumar (2 years ago)
How could time value .75?
rio evi (2 years ago)
awesome, thank you so much
forsakenAdik (2 years ago)
waw! so very helpful, thanks for sharing!!!
John jr (2 years ago)
excellent.....one more question..how do we calculate by having a lunch break say for an hour ?
Dinesh Kumar Takyar (2 years ago)
Have a look at this link: http://www.familycomputerclub.com/excel/using-time-functions-in-excel.html
KcoRdiKjr (2 years ago)
Thanks.Very helpful.
Nirosha Priyadarshani (2 years ago)
Thank you very much Sir, you explained very clearly. its very useful. thanks once again.
Anton Siva (2 years ago)
Thank you very helpful
Dinesh Kumar Takyar (2 years ago)
+Anton Siva Thanks for watching.
Ryan A. Flournoy (2 years ago)
This quick video was the answer to my problem. Thank you!
Abinash Kumar (2 years ago)
Hi Dinesh, I have small query i want to add exp of employees in YY::DD format. For Example if i have to find out total exp of an employee which includes previous exp and current experience. You can consider 2.10 (years n months)as previous exp and current comp exp is 2.11 (years n months) total should be 5.9 (years n months) but when try to sum i am not getting the same value i tried using year and month functions. Can you help me without using any macros.
Abinash Kumar (2 years ago)
+Dinesh Kumar Takyar Thanks! But the video also has the same content, which did not answer my query. Please share me your email ID.
Dinesh Kumar Takyar (2 years ago)
+Abinash Kumar This link might help: http://www.exceltrainingvideos.com/date-functions/ You might also like to checkout the web-links at the bottom of the article.
John Taxpayer (2 years ago)
Hello Dinesh. Quick question. I'm trying to simplify our work schedule, and the data entry needed for my employees, and this is what I have. Presently, we are in Time 13:30 format for the Time In, Time Out, Duration(Out-In) cells. Here is the current punch sequence Time In 8:00 Time Out 16:45 Hours Worked 8:45. How do I change the format in all three cells, so we only have to enter 0800 1645 and only the duration(meaning hours worked cell) shows up as 8:45 with a colon?
33:18:00 45:02:00 01:13:11 01:53:59 01:31:16 37:01:00 how can i add the above in excel ...... plese help urgent
can you please show me a solution on how to do this addition in excel
+Dinesh Kumar Takyar yes i know but it can be assumed in our line of business which is uber and ola
Dinesh Kumar Takyar (2 years ago)
+PARAMOUNT INTERIORS How would you know when it is minutes and when it is hours?
+Dinesh Kumar Takyar Dear DINESH SIR,, This is the format in which i receive data of the duration of time a car is rode. I am confused on how to add this up in excel hence need your help. 33:18:00 (33 min 18 secs) 45:02:00 (45 min 2 secs) 01:13:11 (1 hour 13 min 11 secs) 01:53:59 (1 hour 53 min 59 secs) 01:31:16 (1 hour 31 min 16 secs) 37:01:00 (37 min 1 sec) l please help urgent
Dinesh Kumar Takyar (2 years ago)
+PARAMOUNT INTERIORS These links will help: http://www.exceltrainingvideos.com/calculate-difference-in-hours-between-two-date-time-values/ http://www.exceltrainingvideos.com/payment-calculations-based-on-timestamps-in-excel/
Eric Lenardt (2 years ago)
Dinesh you rock! Thank you.
egwpisteuw (2 years ago)
Felcia Lin (2 years ago)
Thank you very much for this video, Sir. You helped me with my assignments.
Abbad Ali (2 years ago)
Thanks man
Los Pescadores (2 years ago)
That was perfect
Vincent Nathan (3 years ago)
Kindly Explain why you did not convert 75 mins in 1 hour 15 mins kindly re-upload this video with the Auto Sum time option but thanks on the first step
Kareem Mahgoub (3 years ago)
Thanks Mr.Dinesh Kumar Takyar Have a great day always Best, Kareem
Dinesh Kumar Takyar (3 years ago)
+Kareem Mahgoub Thank you!
Control P Advertising (3 years ago)
i cant do it ( the formula you typed contain an error pls help
David Cole (3 years ago)
Is there a way to automatically take out 30 minutes for lunch if the employee works more than 6 hours in a day?
Prakash Vyas (3 years ago)
in your example the actual total is 43:45 hrs. whiloe it display 43.75 (perhaps it will be in decimal ) but what to do if we want it to get total in hr:mm"ss
Margeret Hawthorne (3 years ago)
still don't understand
Nowfar alm (3 years ago)
tnx 2 u
Tenesse Yanez (3 years ago)
Hi Mr. Takyar, I would like to ask how to computed Total Time needed work less Total Time rendered. Take for example, TOTAL HOURS NEEDED TO WORK l TOTAL TIME RENDERED l Lacking Hours 3:14 l 4:00 l ? 3:01 l 4:00 l ? Looking forward to have an immediate response :) Godbless!
kousar tariq (3 years ago)
Thank you soo Much
Jennifer Roya (3 years ago)
Thanks so much Dinesh, I have been really struggling with this. Your explanation was simple and easy to follow, what's more importantly it WORKS!!!
Houssem Kan (3 years ago)
Standard Trevor (3 years ago)
Thank you so much for this.
Tek D (3 years ago)
How we add if we have lets say: (Hr:Min) but want sum in Hours? A1: 12:00 =12hr, 0Min A2: 08:00 =8hr, 0min A3: 00:10 =0hr,10min A4: 00:20 =0hr, 20min Thanks, Tek
Tek D (3 years ago)
+Dinesh Kumar Takyar Thanks sir. And one more question but not related with it: If I am using multiple page in Userform and have Command-Button Called "Next". If I click Next Button on page 1 it should take me to page 2. Is it possible? If yes what would be the VBA coding for cmdNext. Thanks, Tek
Dinesh Kumar Takyar (3 years ago)
+Tek D  Method A =sum(A1:A4) Format the cell with the formula to h:mm Result: 20:30 Method B: =sum(A1:A4)*24 Format the cell as a number with two decimal places. Result: 20.50 http://www.exceltrainingvideos.com https://youtu.be/_d-UGO0JSnk
wastedlove25 (3 years ago)
nice tutorial..thanks!help me alot
Kullakaln G. (3 years ago)
Thank you so much!
Mukesh Punalkar (3 years ago)
Santosh Sawant (3 years ago)
I want time in excel as per system clock
Thank you very much
Marcelino Torrecilla (3 years ago)
Thanks Dinesh, This was very useful
sagar jagtap (3 years ago)
Very Nice...............Thank You Sir...........:)
Vorname (3 years ago)
Nice video; nevertheless two tips and tricks: 1:) instead of an if-formula better use =mod ((endtime minus beginning time;1)). 2. Instead of using number formats try custom format and choose the one that is displayed [h];mm;ss :) works better and is easier:)
Vorname (3 years ago)
+Dinesh Kumar Takyar was a pleasure to me;)
Dinesh Kumar Takyar (3 years ago)
+kathrin9674 Excellent ideas!
tomiasorensen (3 years ago)
Very nice, but. My team can be simple. If I enter 00:00 until 00:00 I get zero hours. How do I get it to take that into account?
10521577A (3 years ago)
I can't hear anything.
Ashish Bansal (4 years ago)
Hi... I am Ashish, working in a company as a supervisor...I need your help pls... I want to keep track on some of my team members in order to gaze how much time they spent on their system.... specialy on excel... Their work is to listen recorded calls and to evaluate the call on some defined parameters... I want something in excel who can tell me that at what time my team member started to evaluate the first call and what time he/she finished and saved overall summary, findings and feedback on excel Same for 2nd, 3rd 4th.....10th, 20th call I want to know how much time my team member takes to evaluate a call... Can u help me pls... My num: 9818866044 Email: [email protected]
Litz93Enzy (4 years ago)
if i type 60 how do i make that in to 1 automatically. e.g. 60min as displaying 1hr. as you can see in you video the total hours 43.75. so if its in time hours and min way it should be 44.15 (as in 44hrs and 15min). i hope you understand what im trying to say and hope you can help me with that. thank. 
kissibrown (4 years ago)
thank you so much. 
Sahil Faizi (4 years ago)
Hi I would like to ask you if i add another cell break time so how can we min the break time from the total time thank you.
Deepan Subramanian (4 years ago)
good thanks
Mohammad Khairul Islam (4 years ago)
thank you sir
johny english (4 years ago)
Awesome video...just had a question, how would you find average time minutes and seconds.
Dinesh Kumar Takyar (4 years ago)
+johny english Have you tried to use the average function? Moreover, time needs to be entered with an a or p for am and pm respectively. Otherwise Excel assumes the time is am.
johny english (4 years ago)
+Dinesh Kumar Takyar an average for the following time. 5:50,6:60,3:30,2:50
Dinesh Kumar Takyar (4 years ago)
+johny english Can you give an example?
Kaviyarasan V (4 years ago)
The exact solution, Very Nice, Thanks a lot
Wayne Johnson (4 years ago)
got to the point,THANKS
Sue Grundy (4 years ago)
thank you for this video. It really helped me
Rdfs Bzs (4 years ago)
And I did find this useful, thank you.
John Kenneth Selidio (4 years ago)
how about if work with a BREAK TIME? how will i get the calculate of that... thanks 
John Kenneth Selidio (4 years ago)
thank you so mucvh
Dinesh Kumar Takyar (4 years ago)
Check this video out:http://www.exceltrainingvideos.com/time-functions/
Dinesh Kumar Takyar (4 years ago)
If you have a break time like teat-time or lunch-break, then enter the data into a cell and take it also into account.
Nikhil C Saha (4 years ago)
you are so great teacher. thank you so much
chandran slevaraj S (5 years ago)
Thank you very much!
Fazrullah Jaini (5 years ago)
Hi, can anyone help me. How to calculate hours between 2 or 3 dates exclude the non working hours? lets say 1/20/2013 11:00PM (case come in after working hours) -> 1/21/2013 10:30 AM (case assist in working hours). Non working hours will be 10:00 PM until 8:00 AM. answer will be 2.5 hours as case come in after working hours, so will start counting from 8AM. How to minus the non working hours. I need the formula. Pls help me. ;(
Genavie Solano (5 years ago)
Thank you very much!
Thank you very much
khamis Al Mazrouei (5 years ago)
smart. easy and basic... thanks dear.. no need for MOD.
Bernardo Pulido (5 years ago)
I really like this it's nice and simple, I am wondering if you could put "day off" at the Start time and still do the calculation, because it's giving #value! error. If possible what would be the new formula?
sonu rawal (5 years ago)
its very nice
Steven Whiting (5 years ago)
How about if you have 30mins or 1hr breaks? Where it doesn't matter when the break was taken, they'll just either be 30mins or 1hr breaks per day.
Steven Whiting (5 years ago)
Nice, clear, short and easy to understand. Just wanted something basic for on my phone, this was what I wanted. Thanks.
Anthony Paul (5 years ago)
HE Has such a beautiful voice and it is easily understood..........its marvelous
gedrick sagum (5 years ago)
im very sorry i cant understand what ur saying coz ur speech was not that clear.. i have one question what is the purpose of the variable 1 ??? i analyze it but it confuse me
Bader alkhmshy (6 years ago)
Hi, how can the total hour be 43:75? while the hour =60 min

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.