Working with Time in Microsoft Excel | Time Functions and Conversion

[@sgorjski]

Thank you! You just saved me SO much time! I needed to show an arrival time 30 minutes before a scheduled time and I wasn't sure how to make that happen - you made it so simple! Thanks again!

[@ChrisMenardTraining]

Glad to help. Thanks, Stacy.

[@rachaelbeede5462]

I am trying to add a column of times (hours and minutes) for payroll not in decimal format. For instance 7:55 seven hours and 55 minutes plus 3:25, plus 4:55, etc. I formatted the cells to [hh:mm] but I still get zero. Do you have any videos on that? Or solutions.

[@pencilpainters]

Thank you….i just started with Excel….i needed this lesson . 👍

[@ChrisMenardTraining]

You are welcome!

[@sukidve1]

Hey Chris. I want to add cumulative hours in Excel. I am able to do it with [h] Format upto 4 digit in hours. What to do for more than that. Like I have 30000:20 hours and minutes and would like to add say another 100 hours to this.

[@chukwudikeofodum6839]

I have time written as 00:30:09 in my rows, but appears as 12:30:09 on the formula bar. How do i fix this? This appears normally on my personal system, but differently as explained above on my work desktop

[@Project1643.]

Can Excel calculate the duration of time over many days? For example, I start my well pump on June 1 at 10 AM and turn it off on June 7 at 3PM. Is there a formula that gives me the amount of hours and minutes the pump was on?

[@philcnc1303]

Hello, If I have to work 12 hours a day (6:00 AM to 6:00 PM). And the company doesn't need me to punch in and out 30 minutes for lunch during the day. What is the formula or how do I need to do it in Excel? Thank you in advance.

[@ChrisMenardTraining]

I combined two screenshots showing the formula and then the format I created. drive.google.com/file/d/17W06EQh3_50Qt4hEkWFqrJZ3TcEQzU_7/view?usp=drivesdk

[@philcnc1303]

@@ChrisMenardTraining - Wow, It looks so simple. - However, Looks like I have to punch out and punch in during the day in order to get the total of 11:30 (11 hours and 30 minutes). In my case, I do not have to punch in and out for lunch at all. - One other thing, does this formula work with Excel 2007? - Because after I try with this formula all I see is ####### no matter what I change or select in the custom, type, h:mm option. Here I include a screen shot so you can see. Thank you very much for your time. (By the way. How I can attach a photo?)

[@firetrucker1000]

What formula can I use to determine if a start time/end time is in AM or PM if it just shows the military time without the AM/PM indicator?

[@Dwavard]

Fantastic - the tip to explaining how to add up time v. helpful :-)

[@ChrisMenardTraining]

Glad you liked it

[@sanchezdora1266]

Thank you Chris! easy and clear explain!

[@ChrisMenardTraining]

You're welcome!

[@jadonanderson]

do you have something about regular time and overtime??

[@NathanRoss-g6e]

Ok, so I have the last issue, but not following where u just “add one” and it fixed your issue. Have 12:30 then 1:15 should be :45, but I get 12:45 when I add one to the formula?

[@maidenfuller9805]

Hi, very smart. How can I display a result in a cell that has a formula displaying time, to display minutes?

[@husamghaffari]

Thanks a lot Sir it solved my problem with in few seconds as your video was on point and not wasting any time

[@اروىالحازمي-ج9ظ]

Hi, if I want to know the difference time between 2 cells is it 15 min or the difference is 30 min what will be the correct function to use

[@ibrahim82649]

thanks you, you just saved me So much time, bless you sir.

[@ChrisMenardTraining]

You're welcome!

[@meniiix]

Hi Chris, new fan here. I'm trying to impute hours and minutes to create a graph of how long I spend time on social media. If I put in 1:45 in a cell it keeps trying to read it as 1:45 am. How can I keep hours and minutes without realating to the time of day.

[@karengerig652]

@Chris, I have the same question.

[@Story_of_a_Faceless_man]

Thank you so much for this amazing information sir… I have a small question please help me to solve it…. If I have a negative time how can I sum that…. For example you have a list of employee and Over Time they did, some of them will be negative hours because for some issues those OT don’t get approved… how can I get a sum of these hours including negative.. simple sum formula don’t get me results and I can’t clean data..

[@ChrisMenardTraining]

I would use SUMIF Here is a small sample I put together. drive.google.com/file/d/1biDbn8xTic5P-hg2Q4cOZpVu7u3FLMZW/view?usp=drivesdk

[@Story_of_a_Faceless_man]

@@ChrisMenardTraining Thank you so much let me try it... you are the best..

[@stevewonderlin5892]

Do you have a video for how to do times for running workout time for different distances?

[@ahmedy.albanna635]

Very well explained, U R exceptional Chris.

[@ChrisMenardTraining]

Thank you kindly! I appreciate it, Ahmed.

[@2badger2]

Great video! How do you subtract 15 minutes from 1:02:00 am? Thanks

[@aymantaher2165]

Man, u're a lifesaver. God bless you

[@ChrisMenardTraining]

Thank you, Ayman.

[@Sassy4u2u]

Thanks so much…you are the only one that I understand.

[@ChrisMenardTraining]

Glad to hear that! Thanks, Marlene.

[@insidehead]

Excellent Chris.

[@ChrisMenardTraining]

Many thanks!

[@akinsamuel2007]

Hi Chris' - I've seen this solution but I sill cant get it to work. I assume it may be a problem with the Version of Excel I'm using being the Chrome in house Google version? Adding up a list of times with a total at the end that doesn't recycle the clock continues to elude me...

[@muhammadrizwan7897]

Hi, Its a great video. I have one question how can i calculate the return time with respect to assign time and our working hours is 8 per day. Thanks in advanced

[@tonytaf107]

Any suggestions to covert for example: 1m 30s to 1.5 or 4m 30s to 4.5 ???? Please

[@kalyanamurti4624]

Hello, How about, i have the START DATE in column A, START TIME in column B, END DATE in column C and END TIME in column D. I need to calculate the Time Elapsed using those 4 data. Please help! Thank you

[@ChrisMenardTraining]

Will this works? drive.google.com/file/d/1_sQAe7kw1ht9OHYYMkXSHFLf55KrAain/view?usp=drivesdk

[@kalyanamurti4624]

@@ChrisMenardTraining It's working, thanks a lot!

[@MostafijurRahman-m9e]

Ticket create date : 05/01/2024 09:30 AM, Ticket closing date: 07/01/2024 12:30 PM, I want the total duration but after 09 pm to 09 am i dont want to count the hours, whats the excel formula. please help.

[@jozefchromy5644]

how about very important topik subtracting time past midnight - he ?

[@loops831]

What’s the formula for a timer for a break aid begin time and return time

[@marcosdellavalle2309]

Great Video, i need help create a formula to convert dollar to time, (eg. $100 to Hours and each $1=1Minute, so $100 looking for end result to be 1:40 minutes)

[@ChrisMenardTraining]

Divide by 1440 and format as time. Here is a screenshot. drive.google.com/file/d/1PK09AzABsEuQB-D0hw1OlZxFq10KUng1/view?usp=drivesdk

[@marcosdellavalle2309]

@@ChrisMenardTraining I knew had to be something easy

[@DemianOleg]

Hi Chris, Error says: *File is no longer available for download.* Please re check the download link. Thanks a lot.

[@Earth-360]

Great one. 🎉

[@ChrisMenardTraining]

Thanks 😁

[@billjuss5331]

Chris M, Love your Video: Thanks for showing some COOOOOoooooL Tricks. Current Time = " Ctl Shift ; " THANK YOU!!!

[@ChrisMenardTraining]

You're welcome!

[@joshprice2846]

Is there a formula for cycle time (Taking the current time - display time in a cell)?

[@ubaidillahmuhammad20]

nice and good job. but please put all excel practiced files in the description....

[@ChrisMenardTraining]

bottom of the blog post chrismenardtraining.com/post/working-with-time-in-excel-time-function

[@kishorborude5036]

sir plz help and slove , =now() current time in cell and manualy time another cell both are similar how to define vlookup , validation , true /false etc

[@Mishkafofer]

Google drive link don't work.

[@abid8168]

Hi Chris, I am your fan from Pakistan, I work in HR Department, I am making employee's time report and checking his actual time in and out Vs scheduled time in and out, I am stuck in the below situation: Cell G7 has scheduled time out value as 20:30, where as cell H7 has end time value as 20:00, if I apply this formula "=IF(H7

[@ubaidillahmuhammad20]

nice and good job. but please put all excel practiced files in the description

[@ChrisMenardTraining]

blog post bottom of the page. chrismenardtraining.com/post/working-with-time-in-excel-time-function

[@swatipanda9891]

Hello. How can we combine csv files in excel in simpler format without vba or any macro?

[@ChrisMenardTraining]

My blog post with the file is here. chrismenardtraining.com/post/working-with-time-in-excel-time-function I have over 200 Excel video. Here is my playlist with the most recent videos at the top. kzbin.info/aero/PLsYjn_fAfG42S1JmTFr7B5eTQRvm5w3sF

[@mesoto18720]

Nice presentation

[@ChrisMenardTraining]

Thanks a lot

[@aviatortrucker6285]

That works fine if you work past midnight within the same 24 hour period. but let’s say you wanted to find out how much time off you had. As an example you got off work at 5:28PM on Tuesday the fifth and you came back to work at 9:03AM on the 12th. How much time were you off?

[@ChrisMenardTraining]

Would this work. Image attached drive.google.com/file/d/1KaCBzlHAackHuFfPJQDgef_5UIDy3UpE/view?usp=drivesdk

[@aviatortrucker6285]

@@ChrisMenardTraining thank you very much that looks like it works, but I also found something a little more simple. You can format the time in AM or PM or in 24 hour format. You must then enter the date and time even though the cell may be formatted to only show the time. The calculation is the later time minus the earlier time. The format of the cell you do the calculation has got to be set up in special format. I found. [h]:mm works best.

[@demetrioskasabalis5536]

Thanks

[@ChrisMenardTraining]

Thank you so much!

[@demetrioskasabalis5536]

@@ChrisMenardTraining You are worth every penny!!! Thank you,

[@mohammadalbizri2013]

Thanksful

[@ChrisMenardTraining]

Thank you.

[@butchmacaylas6352]

TNX.

[@ChrisMenardTraining]

Glad to help.

[@kellyoku]

Nice 1

[@ChrisMenardTraining]

Thank you!

[@WestPointPI]

Private Investigator Here - Q. How to add time in 24 HR clock? When it goes over Midnight on the same day? and then deduct 1/2 hr break?

[@narancs5]

Excel is absolute crap with date and time. I just dont know why they stick to these obscure methods. E.g. counting all time/date with a number originating from 1900 ( having to convert everything to an insanely long decimal which then gets auto-rounded and you lose accuracy when converting back) or when it auto-converts your input to date and when you change the format to normal numbers, your input is corrupted. Thanks for providing an insight and tips!

[@donbleeker8460]

Need to increase a cell by one every new day

[@anwerjaveed6649]

Poor visibility of text

[@ChrisMenardTraining]

I'm not sure what you mean. The text on the video?