Excel Basics #15: Date & Time Format & Calculations

[crnadak87]

Thank you a lot but a have a huge problem!

How to turn off automatic date update, I want excel to enter a date based on if cell is empty or not (IF(isblank(A1)=true,"",today(­)) - but i don't want that date to be updated tomorrow, just to enter a today's date in that cell and to leave it like that for good...

Any ideas...

Btw your videos helped me a lot, already thankful !

[ExcelIsFun]

@crnadak87 , there is no way of preventing TODAY from updating. You would need VBA code. For Q like that post:

mrexcel [dot] com/forum

[crnadak87]

@ExcelIsFun Just as I taught. Thanks

[MrAAAAA1982]

Your Videos are so coooooooooooooool man..

Thanks for all of that.

[1231234akm]

can you help me

[MrPrios1]

Thanks again for your videos. There are a great help even when I use Libre Office.

[ExcelIsFun]

I am glad that they help!

[AMR421RA]

@ExcelIsFun What if you were going to calculate the the total hours for a worker who worked a 3rd shift schedule (e.g. 11:00 pm to 8:00 am). This formula wouldn't work for this example. Can you please tell me how I would do this for a 3rd shift worker?

[ExcelIsFun]

@AMR421RA , try:

=MOD(8:00 AM - 11:00 PM,1)

Here is two videos:

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

Excel Magic Trick 501: Excel Time Format & Calculations (10 Examples)

[AMR421RA]

@ExcelIsFun Thank you I found this out after I wrote this comment. Thanks for the reply back. This helped me out with a project I had for work.

[ExcelIsFun]

@AMR421RA , You are welcome!

[louissjr]

Hi, I have a spread sheet were I'm trying to add a section that shows last activity. Example: All Invoices from the last 30 days, 60 days, etc. Could you help me set it up?

Thanks in advance for your help.

[ExcelIsFun]

I have many videos on this topic. This falls under the topic of "extract data" it is possible to do this with 1) Filter, 2) PivotTable, 3) Formulas, 4) Advanced Filter. 5) D Functions, and more. I have a playlist of videos called "Excel Extract Records From Database Table / List". Search for this and look through the videos. Filter is the easiest method. Also, I will try to make a video about this in the next two weeks that will show all methods together.

[ayhan129]

@ExcelIsFun

hi i have a question.why didn't we use round function?we are working on wages and we are using them in formulas.as far as i remember in this case we need to use the formuLA in round function.

i mean isn't (wage*worked hour).supossed to be round(wage*worked hour)?

best wishes ayhan

[ExcelIsFun]

The search at YouTube does not work that well for playlists. Better to search for "excelisfun", then click on the channel link and watch the video that auto plays - this video shows how to get to the playlists. then you can find the playlist for "Excel Extract Records From Database Table / List"

[ExcelIsFun]

Try this video title:

Excel Magic Trick #151: DATEDIF function (between two dates)

[imrani110]

Thankyou soo much for this video!! It has just saved us a few hours of pulling out hair and getting wrong results. The rest of you videos are amazing too!

From Elaine, Jo and Imran

[ExcelIsFun]

You are welcome!!

[apsjr1]

how do you calculate your lunch time with working time

[ExcelIsFun]

Try these video titles:

Excel Magic Trick 598: Hours Worked In Day Including Lunch Breaks

Excel Magic Trick 727: Calculate Hours Worked Night or Day Shift With Break For Lunch

Excel Magic Trick 718: Calculate Hours Worked (Day or Night Shift) & Subtract Lunch

[Seriouslioness]

THANK YOU VERY VERY MUCH. VERY APPRECIATIVE. Taking a excel class taught by a PC user. Quite different than the excel for mac.

[mica122213]

OMG I found your twin timothy treadwell the Grizzly Man diaries. You are a great teacher but you you just like him. Love the videos

[ExcelIsFun]

I am glad that the videos help!

[cnunez2008]

thank you soooooooooo much for this i wish i could have watched this video before goin in to work today ahhhhhh

[ExcelIsFun]

You are welcome!

[Maithos]

It is 1491 day pass due now. lol.

[ExcelIsFun]

We will have to charge them a lot of interest!!

[RockyTeya]

thanks :)

[ExcelIsFun]

You are welcome!

[yosoyisrael]

Thanks so much for your videos. I have a question... You probably answer it in this video, but is not clear to me. In this video you the quantity of days that's over due...but what if you don't want it to be over due and you want it to show the quantity of days before it's over due.. what's the formula for that...

if you have today's date on A4 and the due date on A10 what's the formula for that i tried =(A10-A4) but it's not it... can you help??? THANKS ALOT. !!!!!!!

[ExcelIsFun]

Here is a formula for "Days Until Due":

=IF(A10-A4>=0,A10-A4,"Past Due")

[ExcelIsFun]

Here is a formula for "Days Past Due":

=IF(A10-A4<0,A4-A10,"Not Due Yet")

[yosoyisrael]

thanks!!!!!!

[ExcelIsFun]

You are welcome!

[ExcelIsFun]

The two formulas go in different cells.

(I had to post three comments becasue YouTube is so difficult to use when it comes to comments).

[ExcelIsFun]

Another way is to put this label formula in cell C12:

=IF(C13>0,"Days Past Due","Days Until Due")

This this calculating formula in cell C13:

=A4-A10

[dreamingWisdom]

I have two questions:

Is there a way to make the default time output of the NOW() function have the seconds, i.e., HH:MM:SS, instead of just HH:MM?

Suppose I have a variable "rate of production" and a variable desired amount of something, 3 cells each holding the rate, desired amount, and the time it will take to reach the desired amount.

How can you express the time as "A years, B months, C days, D hours, E minutes, F seconds"?

[ExcelIsFun]

I do not know. You should post your question to the Mr Excel Message Board (they are the smartest Excel people that I know).

[ExcelIsFun]

Rad

[catalani2001]

thank you very much! great tutorial, by the way.

[ExcelIsFun]

You are welcome. Be sure to serach YouTube for "excelisfun" and then go to the excelisfun channel and watch the video that automatically plays. That video will show to how to search for the exact video that you want.

[catalani2001]

When scheduling the employees for my department, I put in the in and out times in the same column i.e. 8a-5, 7a-3p. Can I cross reference what I type with a formula such as the one you provided so every time I type in 8a-5p it will automatically calculate 9 hours worked? I hope this makes sense to you....thank you.

[ExcelIsFun]

In time out is in A1 and time in is in A2, then use:

=(A1-A2)*24

or earlier formula:

=(TIMEVALUE("5:00 pm")-TIMEVALUE("8:00 am") )*24 (not a good formula)

[catalani2001]

Hello, how can I calculate the time in and time out without entering the information on different cells. Can I type in 8a-5p for instance and still be able to calculate the number of hours worked each day?

[ExcelIsFun]

I am not sure why you would want to do that. But here is a method:

=TIMEVALUE("5:00 pm")-TIMEVALUE("8:00 am")

[thirthyest]

so in the end when we have

gross

=E2*B2

should we also use

=ROUND(E2*B2,2)

because it's about money?

[ExcelIsFun]

The decision always is based on three rules:

1) Operation is multiplying or dividing

2) You are required to round (money only has pennies)

3) You will use formula result in subsequent formula

If you make a calculation (1st two rules), but you will never use the formula result in subsequent calculations, formatting to see only two decimals will suffice.

[thirthyest]

i think i'm getting it

thanks

[alg123]

If you came to Mexico, you´ll see that Its possible work from 10:00 am to 11:00 pm (13 hours a day) and earn that amount of money!!!

[boricuapapi224u]

Awesome video very helpful /I have a question hopefully u can help

I need to show date and time in a chart to show trend for ex: 7/1/2009 2:33,13:20,1820 any ideas

and so on for the whole month of july

[ExcelIsFun]

Type your labels into cells (labels like 7/1/2009 2:33,13:20,1820), then add them to chart. Excel 2003, use step 2 of Chart Wizard. Excel 2007, ChartTools Design ribbon/tab, Select Data button.

[bmd4854]

Hi, your videos are very helpful! I have a question that's related to this video. Is there a way to make cells default to PM rather than AM? Thank you!

[ExcelIsFun]

Dear bmd4854 ,

I do not know how to do that!

--excelisfun

[monirkhan007]

i know how to do that. use this formula

=B1-A1+IF(B1<A1,1)

[morecambeboy]

This a very huge compilation work, you are the most merciful person for all of us who have no expertise in excel.

God bless you

[ExcelIsFun]

Dear morecambeboy,

I am glad that the videos are useful1

--excelisfun

[slaves2sin]

I am assuming that if i press crl and + and ; and a date comes out, then this date will remain if i open the spreadhseet tomrw. but if i use the today function, the date will change each time i open the spreadsheet on successive days?

[ExcelIsFun]

Dear slaves2sin ,

That is correct!

--excelisfun

[macyomi]

Hiya how do you calculate age using this context?

[ExcelIsFun]

Dear macyomi,

Age in years is this year minus the year you were born.

If your birthday in is A1, you could use this also:

=(TODAY()-A1)/365.25

--excelisfun

[cowspoo]

I want to tell you that these videos are a huge help. I've learned to love excel because of my Accy classes! Of course, I have made excels for my own projects and i've learned many of your tricks! Thanks!

-Steve

[ExcelIsFun]

Dear cowspoo,

I am glad that the videos help!

--excelisfun

[seaice1978]

I THINK HE MEANS TERRIFIC. foreigners sometimes confuse these two words.

[ExcelIsFun]

Dear seaice1978,

Thanks for the tip!

I hope you enjoyed the video!

--excelisfun

[Baobao531]

woah, it is terrible.

Woah.

Thanks for posting.

[ExcelIsFun]

Dear Baobao531,

What is terrible?

--excelsifun