Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
10 Cards in this Set
- Front
- Back
|
DATES |
- Excel assigns a number for dates. Dates begin from 01/01/1900 = 1.
- To convert date 01/01/1900 to a number, enter "," over the cell
- Excel currently uses previous centuary for years 31 to 99 and current centuary for 1 to 30.
- You can add or subtract days
|
|
|
TIME |
- Simplest form = 8:45 AM - When you convert time to a number (,) it will divide by 24. Thus 8.45 AM = 0,32 (8.45/24)
- You can add or subtract time
|
|
|
TODAYS DATE and TIME (shortcut)
CURRENT DATE and TIME (shortcut)
|
TODAYS DATE and TIME (This figure is STATIC - does not change) DATE - Shortcut = Ctrl + ; TIME - Shortcut = Ctrl + Shift + ;
CURRENT DATE and TIME (changes with time) DATE = DATE() TIME = DATE - Shortcut = Ctrl + ; TIME - Shortcut = Ctrl + Shift + @
|
|
|
WEEKDAY
Find out if date falls on weekday |
=WEEKDAY(Cell1)
Cell1 = 1/1/15 weekday(cell1) = 5 =Thursday
you can now format cell1 5 by going to R -> Home -> Number and customise 5 by typing ddd (3 letters) of dddd full day.
|
|
|
WEEKDEND
- manipulating data to fall on weekday |
-You may need to exclude days falling on weekends. You can include an IF FORMULA to change a WEEKEND outcome.
- If weekday(cellx) = 7, then subtracting 1 so it can fall on Friday. If weekday(cellx) = 1, then you can add 1 so the day will fall on Monday. |
|
|
NETWORKDAYS
To calculate number of workdays between start and end dates. You can exclude weekends and holdays. |
NETWORKDAYS (startdate,endate,selectrange)
-Tabulating differences between selected dates for project or transaction. You also exclude weekend and any public holidays.
-Selectrange Create a range that lists the dates that need to be excluded |
|
|
INTERNATIONAL WORKING DAYS
To change the days that are considered weekend as per different countries
|
=NETWORKDAYS.INTL(startdate,enddate,presented with option for weekends) |
|
|
DATEDIF |
DATEDIF(startdate,endate," ")
-select y / m / d / ym / yd / md - must put in parenthesis
-there is a bug md |
|
|
Cleaning up Time and Date data
2 methods |
Method 1
Date = DATEVALUE(cell1) Time = TIMEVALUE(cell1)
Method 2
performing a mathematical operation eg adding 0 to data - Date = Ctrl + shift + # -Time = Ctrl + shift + @ |
|
|
EDATE
EOMONTH |
-Work out an end date based on number of months. Given start date and number of months
EDATE (Startdate,monthno) startdate = 5 Feb 14 month no = 5 Edate = 4 July 14
. -This will calculate the last day of the month
EOMONTH(Startdate,monthno) EOMONTH = 31 July 14
|
