Microsoft Office 2013最新教程 (6)

Microsoft®OfficeExcel®2007TrainingLearnhowtofigureoutdatesusingformulasinExcel[Yourcompanyname]presents:LearnhowtofigureoutdatesusingformulasinExcelCoursecontents•Overview:It’sadate•Lesson1:WhatdatesmeantoExcel•Lesson2:Calculatedatesbyusingformulas•Eachlessonincludesalistofsuggestedtasksandasetoftestquestions.保险超市’sadate•Howmanyworkdaysuntilvacation?What’sthefinishdateforaproject?•Inthiscourse,we’llshowyouhowtouseformulastofindthenumberofdaysbetweendates.•Youcancalculateprettymuchanythingusingtheseformulas—andwe’llevenshowyouhowtodeterminehowmanydaysoldyouare.LearnhowtofigureoutdatesusingformulasinExcelCoursegoals•Useformulastofindthenumberofdaysbetweendates.•Determinethedatethatfallsafteranumberofworkdays.•Findadatethatfallsafteranumberofyears,months,ordays.Lesson1WhatdatesmeantoExcelLearnhowtofigureoutdatesusingformulasinExcelWhatdatesmeantoExcel•There’sadifferencebetweenwhatdatesmeantoyouandwhattheymeantoExcel.12Toyou,8/22/2010isamonth,day,andyear.ToExcel,8/22/2010isserialnumber40412.•Inthislessonyou’lllearnhowExcel’sviewofdatesmakesitpossibletododatearithmetic,andhowtoenterdatesinaformatthatExcelcanunderstand.LearnhowtofigureoutdatesusingformulasinExcelThecalendarstartsin1900•DatesarestoredinExcelasserialnumbersstartingwith1,whichrepresentsJanuary1,1900.That’swhentheExcelcalendarstarts.•Eachdayafterthataddsonenumbertothesequence.Forexample,January2,1900isstoredas2.•Thatmeansifyoutype8/22/2010,Excelstoresthedateas40412—or40411daysfromJanuary1,1900.LearnhowtofigureoutdatesusingformulasinExcelThecalendarstartsin1900•StoringdatesasserialnumbersmakesitpossibleforExceltododatearithmetic.•Tofindthenumberofdaysbetweentwodates,forexample,Excelsubtractsoneserialnumberfromanother.LearnhowtofigureoutdatesusingformulasinExcelHowExcelrecognizesdates•Whetheryoutype8/22/2010,22-Aug-2010,orevenAugust22,2010,it’sallstoredasserialnumber40412.•TheexceptionisifyouuseaformatthatExceldoesn’trecognizeasadate.•Forexample,ifyoutypedAugust-22,2010or8,22,10,Excelwouldstorethatinformationasplaintext,notaserialnumber.LearnhowtofigureoutdatesusingformulasinExcelHowExcelrecognizesdates•Here’showyoucanchangetheformat—orseesomeoftheotherwaysyoucanuseslashesorhyphenstoformatdates.12SelectDateintheCategorylist.SelectadateformatintheTypelist.•Right-clickacellwithadateinit,andchooseFormatCells.Thenyoucan:LearnhowtofigureoutdatesusingformulasinExcelSuggestionsforpractice1.Formatadateasaserialnumber.2.Formataserialnumberasadate.3.Selectdifferentdateformats.4.Enterthecurrentdate.•Onlinepractice(requiresExcel2007)LearnhowtofigureoutdatesusingformulasinExcelTest1,question1•WhichofthesedateswillExcelstoreasplaintextratherthanasaserialnumber?(Pickoneanswer.)1.June23,20122.23-June-123.June232012LearnhowtofigureoutdatesusingformulasinExcelTest1,question1:Answer•June232012.•ThisdatedoesnotcontainanyformattingthatExcelrecognizesasadate.LearnhowtofigureoutdatesusingformulasinExcelTest1,question2•Whatwouldhappenifyouusedaformulatodomathwithdates,butoneofthedateswastypedinaformatthatExceldoesnotrecognizeasadate?(Pickoneanswer.)1.Excelwoulddisplayamessagetellingyoutoreformatthedate.2.Excelwoulddisplayanerrorvalue.3.Excelwouldreformatthedateintothecorrectformat.LearnhowtofigureoutdatesusingformulasinExcelTest1,question2:Answer•Excelwoulddisplayanerrorvalue.•Excelwoulddisplay#VALUE!insteadofaformularesult.Inthiscasetheerrorvaluemeansthatacellintheformulacontainstextinsteadofadate.LearnhowtofigureoutdatesusingformulasinExcelTest1,question3•8/22/2011and22-August-2011arestoredasdifferentserialnumbers.(Pickoneanswer.)1.True.2.False.LearnhowtofigureoutdatesusingformulasinExcelTest1,question3:Answer•False.•Samedate,samenumber.Lesson2CalculatedatesbyusingformulasLearnhowtofigureoutdatesusingformulasinExcelCalculatedatesbyusingformulas•TodoarithmeticinExcel,youuseformulas.•Inthislesson,you’lllearnhowtouseasimpleformulatofindthenumberofdaysbetweentwodates,andyou’llseewhyit’sveryimportanttotypedatessothatExcelcanrecognizethem.FunctionUsedtofindNETWORKDAYSNetnumberofworkdaysbetweentwodatesWORKDAYDatethatfallsaspecifiednumberofworkdaysafteranotherdateDATEDatethatfallsaspecifiednumberofyears,months,ordaysafteranotherTODAYCurrentdateLearnhowtofigureoutdatesusingformulasinExcelFindthenumberofdaysbetweentwodates•ImaginethattodayisJune9,2010.YourvacationstartsonAug.21,2010.Youwanttofindouthowmanydaysthereareuntilvacation.Here’showtodothemath.123Typeasimpleformulaintoanemptycell:=A3-A2Theresultisthatthereare73daysbetweenthetwodates.Theformulaisvisibleintheformulabarnearthetopoftheworksheetwheneveryouselectthecellthatcontainstheformula.LearnhowtofigureoutdatesusingformulasinExcelFindthenumberofworkdaysbetweentwodates•Findingthenumberofworkdays(MondaythroughFriday)betweentwodatescan’tbedonebysimplysubtractingonedatefromanother.•ItrequiresaprewrittenformulacalledtheNETWORKDAYSfunction,whichcalculatesthenetnumberofworkdaysbetweentwodates.LearnhowtofigureoutdatesusingformulasinExcelFindthenumberofworkdaysbetwee