Microsoft Excel ACM Quick Tip - Date and duration functions
ฝัง
- เผยแพร่เมื่อ 11 ก.ย. 2024
- Welcome to an ACM quick tip video. This lesson will be on how to utilize some of the date functions in Microsoft Excel.
-The first one is the TODAY function. This formula returns the serial number of the current date. The serial number is the date-time code that is used for many time and date calculations... the number comes from how many days since January 1, 1900.
-If you want to show the date in year, month, day format, right click within the cell and select FORMAT CELLS. Now find the DATE category on the left, then select which format you’d like to view it as.
-To find out how many years someone has been employed, or any amount of time between a specific date and today... Type in = SIGN, PARENTHESIS, the TODAY FUNCTION, then MINUS sign, select the cell where the date is, then END PARENTHESIS. Now type FORWARD SLASH (or divide) by 365. Hit ENTER.
-Next quick tip is to find what day of the week some of these dates are. Type in = SIGN, TEXT, PARENTHESIS, click on the cell with the date, COMMA, QUOTATION MARKS, lowercase d, END QUOTATION MARKS AND END PARENTHESIS. With one or two lowercase d’s, it just provides the day in the month, but 3 or 4 d’s give you a Monday, Tues, Weds, etc..
-Another date function in excel is the DATEDIF function. This formula calculates the number of days, months, or years between two dates. Basically the duration. Pick a cell and type in EQUAL SIGN, DATEDIF, PARENTHESIS, select the first date, COMMA, then select the second date, COMMA, then in QUOTATION MARKS, type in either d for days, m for month or y for year. Then END PARENTHESIS.
-This last tip helps with finding the end date of a project if you know the project duration and you’re working within a typical 5 day workweek. The cool addition to this formula is that you can add additional days that you don’t want included in a given workweek (holidays, vacation, etc). For this formula type in = SIGN, WORKDAY, PARENTHESIS, the first criteria you select a start date of your project, then hit COMMA, the next criteria is the total project duration. This number will only be included in Monday-Friday. Then hit COMMA, and the last criteria are the days you don’t want included. After that, end PARENTHESIS.
-Don’t forget, if something’s not working it might be that the CELL FORMAT is not in the correct date or number category!
At Administrative Controls Management (ACM) we aim to provide you and your team with the necessary tools and knowledge to carry out your project successfully. We are here to help with any of your project's needs such as planning and scheduling, estimating and cost engineering, project management, project representation, training, construction delay claims, and consulting.
Our TH-cam channel will provide you with quick tips and tutorials on how to use popular software such as Microsoft Excel, Microsoft Project, Primavera P6, and SAP.
ACM is a certified woman-owned business providing value added project management and controls services since 1985. ACM’s team of highly qualified professionals support a wide range of industries such as heavy civil, oil & gas, manufacturing, university construction, healthcare, and nuclear power. ACM is the first Approved Education Provider as certified by AACE International and a Project Management Institute Global Registered Education Provider.
For more information on how ACM can help you or your business, please visit acmpm.com. Don’t forget to hit the subscribe button if you want to stay up to date with everything that ACM has to offer.
Thanks for watching!
#Excel #Date #Duration