MAKING A SIMPLE TIME IN, TIME OUT COUNTER in MS Excel

MAKING A SIMPLE TIME IN, TIME OUT COUNTER

1.

2. Apply Formatting accordingly
A1 - Category: Date, Type: 3/14/01
B1 - Category: Time, type: 1:30 PM
C1 - Category: Time, type: 1:30 PM
D1 - Category: Number, use two decimal places

3. Input Date, Time in and Time out

4. For cell D2, apply the following formula
=IF(C2=B2, 24,MOD(C2-B2,1)*24)

STEVE CHASE' COUNTDOWN TIMER in MS Excel

STEVE CHASE' COUNTDOWN TIMER

1. Type the labels in A1 through A4
A1 = Date and Time today
A2 = Hour
A3 = Minute
A4 = Second

2. Enter the formulas in column B
B1 =NOW()
B2 =HOUR(B1)
B3 =MINUTE(B1)
B4 =SECOND(B1)

*Note: The NOW function displays the current date and time. Pressing F9 key will update the value.
The HOUR function displays the current hour expressed in Military Time; Zero is 12:00 AM and 23 is 11:00PM

3. Type these labels in D1 though D4
D1 = Event Date
D2 = Event Hour
D3 = Event Minute
D4 = Event Second

4. Type in the datails of the Event Schedule in cells E1 through E4

5. Type these labels in cells G1 through G4
G1 = Days
G2 = Hours
G3 = Minutes
G4 = Seconds

6. Type in the FORMULAS in H1 through H4
H1 =INT(E1-B1)
H2 =IF(E2>=B2,E2-B2,24-B2+E2)
H3 =INT(E3-B3)
H4 =INT(E4-B4)

7. Make the Display by using this function
="Just"&H1&"Days"&H2&"Hours"&H3&"Minutes"&H4&"Seconds Left"

Date and Time Functions in MS Excel

Overview of Dates and Time in Excel
Objectives:
* Understand how Date and Time Functions are used in Excel
* Create a Database using Date and Time Functions
* Create a Countdown Timer with Date using Excel Date and Time Functions

DATES IN EXCEL
* Excel Dates can be added, subtracted and included in other calculations
* Excel stores dates in "Assigned Serial Numbers"

1 - assigned Serial Number for Date "January 1, 1900" for WINDOWS
1 - assigned Serial Number for Date "January 1, 1904" for MAC

Question:
What Date represents 30240?

Exercise 1.a. Convert these numbers into Date
143
14344
54

Exercise 1.b. Convert these Dates to Numbers
1 April 14, 2017
2 July 5, 1984
3 March 11, 2015

TIME IN EXCEL
*Times are stored as "Decimal Numbers" between 0.0 and 0.99999
*Excel stores dates in "Assigned Serial Numbers"

0.0 - 00:00:00 (12:00:00 AM)
0.99999 - 11:59:59 PM)

Exercise 1.a. Covert these Decimal Numbers to Time
1 0.4375
2 0.47197
3 0.50347

DATE FUNCTIONS USED IN EXCEL
* NOW() and TODAY () Function
=NOW() - displays current date and time
=TODAY() - displays current date only

* Determining the age based on Birthdate
=int(yearfrac("Cell Number of Birthdate",TODAY()))
--INT (integer-number) Rounds the number to the nearest integer
--YEARFRAC (year fraction) Returns the year fraction representing the number of whole
                   days between start_date and end_date

* Determining if Minor/Adult
=if(int(yearfrac("Cell Number of Birthdate",TODAY())) <18, "Minor","Adult")

* Determining the age on a specific date
=int(yearfrac("Cell Number of Birthdate",DATE(Year,Month,Day)
--MONTH should be in number EXAMPLE: October is 10, January is 1

CALCULATING RETIREMENT DATE IN EXCEL
* Create the table below:

* Determining the Retirement Date (END DATE)
=EDATE("reference cell for birthdate",12*65)

* Determining the number of Years before Retirement
=YEARFRAC(TODAY(), reference cell for end date)

* To Display only the year in the Retirement Date
=YEAR(EDATE(reference cell for birthdate, 12*60)