Excel Magic Trick 286: MOD function & Time Calculations (Time For Night Shift, or Negative Time)

[harrybkidd]

Hi - used your formula and it work easily, BUT it give Hours and a percentage of hours (i.e. 1.25) not hours and minutes (1:15) is there away to have the result express in hour:minutes?

[falcoperegrinus82]

Thanks! This was driving me crazy until I found this video.

[ExcelIsFun]

You are welcome!

[mesq007]

Dear Mike,

May I suggest another solution. Just add one "day" if the second time is earlier than the first one.

=SE(C13>C12;C13-C12;1+C13-C12)

Thanks

[ExcelIsFun]

Nice!

[aron925r]

ITS A GREAT VIDEO I LEARNT A LOT!! JUST ONE QUESTION, IM TRYING TO USE THE CTRL+SHIFT+~ FOR THE SHORTCUT AND IT DSNT ALLOW ME TO DO IT. DO U HAVE ANY SUGGESTIONS? THANKS AGAIN FOR THE VIDEO VERY HELPFUL! :)

[ysadstore]

@dirtylittlecritter yeah. exactly is =mod(1st time - 2nd time,1)

[cumnkisme]

Dear Mike,

Dates and times in Excel must be positive values. ... Had this trouble though the formula is correct, although the result is a negative. Thank you so much for this great tutorial it helps a lot. Even how much i research in internet no one explain it better than you! You're the BEST!!! Thank you so much!!!

--Rhea Bruna

[ExcelIsFun]

I am glad that the video helps!!

[dirtylittlecritter]

Needed a quick formula and this tutorial showed me exactly what I needed. Great vids and keep posting. Thanks!

[ExcelIsFun]

You are welcome!

[Staftrax]

Thanks very much, I've been trying to solve the time calculation problem for years.

I have a formula which works 95% of the time but your solution is a real peach.

[ExcelIsFun]

Dear Staftrax,

I am glad that this helps!

--excelisfun

[robw1031]

You might want to try moving your mic if you can. you could also try a mic stand with a pop screen i.e. you pop your Ps and slur your Ss this is what causes the thunder and hissing sounds in your videos.

[ExcelIsFun]

Dear robw1031,

Thanks for the advice. I will try and make the improvements!

(I always thought that the thunder and hissing sounds was just Excel doing its calculations ;)  ).

--excelsifun

[givingstars]

Hey Excelisfun,

I had a question on changing dates from mmddyy to mm/dd/yyyy. I have original date of birth data entered as mmddyy (text) and when I format it gives me mm/dd/20xx (date) which should be mm/dd/19xx.

I also have a list of date of hire that is in the same format as above and when I try to format it, it gives me mm/dd/19xx. 

Is there a way to change the format to include only 1900 or 2000?

[ExcelIsFun]

Dear givingstars,

If you type 1/1/30, Excel puts: 1/1/1930

If you type 1/1/29, Excel puts 1/1/2029

In order to solve this you would need to tell me: if you have a column of dates like mmddyy, how do you know which ones are 1900 and which are 2000?

--excelisfun

[ExcelIsFun]

Dear givingstars,

If they are all 1900 and your dates are in the A column, then use this formula:

=DATEVALUE(LEFT(A1,LEN(A1)-2)&­"19"&RIGHT(A1,2))

Then Copy, Paste Special Values.

Then Format the cells with Dates Number Format

--excelisfun

[ExcelIsFun]

Dear givingstars,

If it is a matter of entering dates, then use a four digit year when entering. Then Excel knows what to do.

--excelisfun

[ExcelIsFun]

Dear givingstars,

I just asked this question at the Mr Excel Message Board and Andrew Poulsom suggested this fast and short formula:

=SUBSTITUTE(A1,"/","/19",2)

Now that is a totally cool formula!

--excelisfun

[ExcelIsFun]

Dear givingstars,

Here is still another one!

barri Houdini at the Mr Excel Message Board suggested this formula:

=REPLACE(A1,LEN(A1)-1,0,19)+0

When you have a zero as the third argument, REPLACE inserts characters into a series of characters.

--excelisfun

[planiolro]

The video is awesome since is explaining the reason of using MOD function.I suppose that the same principle is applied for the calculation of differences between dates when you want to find out the numbers of completed years, months or days.However for me calculation of days is difficult.=C5-MIN(DATE(YEAR(C5­),MONTH(C5)-(DAY(C5)< DAY(B5))+{1,0},DAY(B5)*{0,1}))­.The usage of curly brochettes and MIN function confuses me!Maybe you can explain it in a future video dedicated to this topic.Thank you.

[ExcelIsFun]

Dear planiolro ,

You must go and ask barri houdini what it means. He is the Excel master that made that formula at the Mr Excel Massage Board. He knows more than I do about how that formula works. Also, read what he wrote at that post, because he pointed out that because you have different units, depending on what formula you use and assumptions you make, there will always be debate about what the right answer is.

--excelisfun

[tirathmistry]

I give you No 1 to you who are superb, awesome, enthusiastic, claver Tutor

We (your subscriber) are so lucky who always receive such a valuable tutorial video.

God Bless you excelIsFun. Keep doing good work god is always with you Sir

&

Thanks to mrexcel also who also make such a awesome tutorial also :-)

[tirathmistry]

Awesome tutorial video ExcelIsFun

Thanks so much for your time and effort for making such are useful & powerful tutorial video for us

I learn so much trick & tips & most important keyboard short cut because of you

Your all tutorial are awesome & give them all 5 stars tutorial

Thanks

[PrincessWithSkills]

Hi there,

I cannot find Magic Trick #100.

Thanks! ( I only have 548 videos to go)

[ExcelIsFun]

Dear PrincessWithSkills ,

Just go to the playlist for the first 200 Magic Tricks and it is on the 5th or 6th page.

Search for and watch this video to learn how to use the excelisfun channel (including findfing Playlists):

Search For Excel Videos Download Excel Workbooks excelisfun

--excelisfun