Scribd Logo
Upload

Welcome to Scribd!

  • 12 views

    Session 7

    Uploaded by

    aman.1640022
    Here are the answers to your questions: 1. Today's date: 11/23/2023 Shortcut Key: =TODAY() 2. The TODAY() function can be used to get only today's date. 3. Today's time: 11/23/2023 15:26 Shortcut Key: =NOW() 4. The NOW() function can be used to get the current time. 5. To get only today's time without the date, use the TIME() function. 6. Date of Birth: 1/5/1995 Your age in years: 28 Your age in years, months, days: 28 Years, 10 Months, 18

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    Session 7

    Uploaded by

    aman.1640022
    12 views14 pages
    Here are the answers to your questions: 1. Today's date: 11/23/2023 Shortcut Key: =TODAY() 2. The TODAY() function can be used to get only today's date. 3. Today's time: 11/23/2023 15:26 Shortcut Key: =NOW() 4. The NOW() function can be used to get the current time. 5. To get only today's time without the date, use the TIME() function. 6. Date of Birth: 1/5/1995 Your age in years: 28 Your age in years, months, days: 28 Years, 10 Months, 18

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Here are the answers to your questions: 1. Today's date: 11/23/2023 Shortcut Key: =TODAY() 2. The TODAY() function can be used to get only today's date. 3. Today's time: 11/23/2023 15:26 Shortcut Key: =NOW() 4. The NOW() function can be used to get the current time. 5. To get only today's time without the date, use the TIME() function. 6. Date of Birth: 1/5/1995 Your age in years: 28 Your age in years, months, days: 28 Years, 10 Months, 18

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    12 views14 pages

    Session 7

    Uploaded by

    aman.1640022
    Here are the answers to your questions: 1. Today's date: 11/23/2023 Shortcut Key: =TODAY() 2. The TODAY() function can be used to get only today's date. 3. Today's time: 11/23/2023 15:26 Shortcut Key: =NOW() 4. The NOW() function can be used to get the current time. 5. To get only today's time without the date, use the TIME() function. 6. Date of Birth: 1/5/1995 Your age in years: 28 Your age in years, months, days: 28 Years, 10 Months, 18

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    of 14

    DATE TIME FUNCTIONS

    TODAY
    NOW
    DAYS
    DATEDIF
    NETWORKDAYS | NETWORKDAYS.INTL
    DATE
    YEAR
    MONTH
    DAY
    EOMONTH
    TIME
    HOUR
    MINUTE
    SECOND
    DATEVALUE
    TIMEVALUE
    Reading the date 1/2/2023

    Current Date 11/23/2023 10 days from today 12/3/2023


    Current Time 20:26 12 Hours from now 11/24/2023 4:54

    Start Date 2/20/2022


    End Date 2/25/2023
    Date Difference 370 370
    In Years 1
    In Months 12
    In Days 370

    Unit Returns
    "Y" Difference in complete years. The function returns the number of complete years in th
    "M" Difference in complete months. The function returns the number of complete months
    "D" Difference in complete Days. The function returns the number of complete Days in the
    "MD" It calculates the difference between the days in start_date and end_date. The months
    "YM" It calculates the difference between the months in start_date and end_date. The days

    Net Working Days List Of Holidays


    318 1/2/2023

    Weekend number Weekend days String


    1 (default) Saturday, Sunday 0000000
    2 Sunday, Monday 0000011
    3 Monday, Tuesday 1010100
    4 Tuesday, Wednesday
    5 Wednesday, Thursday
    6 Thursday, Friday
    7 Friday, Saturday
    11 Sunday only
    12 Monday only
    13 Tuesday only
    14 Wednesday only
    15 Thursday only
    16 Friday only
    17 Saturday only
    12/30/1899 1900
    12/31/1899 1904
    1/1/1900
    1/2/1900
    1/3/1900
    1/4/1900

    20/02/2023 12:00 PM

    DATEDIF
    1/31/2023

    er of complete years in the period.


    mber of complete months in the period.
    er of complete Days in the period.
    nd end_date. The months and years of the dates are ignored here.
    e and end_date. The days and years of the dates are ignored here.

    Weekend days
    No Weekends
    Saturday, Sunday
    Monday, Wednesday, Friday
    Publisher Genre Date Qty Unit Sales
    Nintendo Sports 1/22/2020 10 ₹ 137,853.00
    Nintendo Shooter 1/23/2020 12 ₹ 132,117.00
    Nintendo Puzzle 1/24/2021 20 ₹ 106,470.00
    Nintendo Shooter 1/25/2023 13 ₹ 136,030.00
    Berkeley Simulation 1/20/2020 16 ₹ 143,686.00
    Berkeley Racing 1/21/2021 10 ₹ 105,918.00
    Avanquest Software Shooter 1/17/2020 15 ₹ 148,762.00
    Avanquest Software Puzzle 1/18/2021 14 ₹ 123,919.00
    Avanquest Software Shooter 1/19/2023 15 ₹ 121,464.00
    Avanquest Simulation 1/14/2020 10 ₹ 149,433.00
    Avanquest Racing 1/15/2021 14 ₹ 102,671.00
    Avanquest Sports 1/16/2023 18 ₹ 119,780.00
    Avalon Interactive Shooter 1/11/2020 14 ₹ 120,131.00
    Avalon Interactive Puzzle 1/12/2021 16 ₹ 123,919.00
    Avalon Interactive Shooter 1/13/2023 17 ₹ 132,522.00

    Count of Publishers where date is more than 15th Jan 2023


    3
    1/15/2023

    Date 2/20/2023
    Year 2023
    Month 2
    Day 20
    2020 2023 FALSE
    2020 2023 FALSE
    2021 2023 FALSE
    2023 2023 TRUE
    2020 2023 FALSE
    2021 2023 FALSE
    2020 2023 FALSE
    2021 2023 FALSE
    2023 2023 TRUE
    2020 2023 FALSE
    2021 2023 FALSE
    2023 2023 TRUE
    2020 2023 FALSE
    2021 2023 FALSE
    2023 2023 TRUE
    Publisher Nintendo

    Genre Date Total Sales Start Date


    Sports 1/22/2023 ₹ 137,853.00 1/1/2023
    Shooter 1/23/2023 ₹ 132,117.00
    Puzzle 1/24/2023 ₹ 106,470.00
    Simulation 1/25/2023 ₹ 136,030.00
    Sports 2/20/2023 ₹ 143,686.00
    Shooter 2/21/2023 ₹ 105,918.00
    Puzzle 2/17/2023 ₹ 148,762.00
    Simulation 2/18/2023 ₹ 123,919.00
    Sports 3/19/2023 ₹ 121,464.00
    Shooter 3/14/2023 ₹ 149,433.00
    Puzzle 4/15/2023 ₹ 102,671.00
    Simulation 4/16/2023 ₹ 119,780.00
    Sports 5/11/2023 ₹ 120,131.00
    Shooter 5/12/2023 ₹ 123,919.00
    Puzzle 6/13/2023 ₹ 132,522.00
    Simulation 6/12/2023 ₹ 123,919.00

    Total Sales in Jan


    ₹ 512,470.00

    Total Sales in 1st Qtr


    ₹ 1,305,652.00
    No Of Months End of Month
    0 1/31/2023
    1 2/28/2023 Sid
    2 3/31/2023 Rout
    -1 12/31/2022 Sid Rout
    5 6/30/2023 CTRL
    SHIFT
    :
    Employee Name Shift Type Log In Time Log Out Time
    Anjali Ray Day 9:00:00 17:00:02
    Suraj Cheema Day 9:00:45 16:45:51
    Rajesh Rai Day 9:01:23 16:59:59
    Aditi Chaudhuri Day 8:59:59 17:01:35
    Sid Rout Night 21:00:00 5:00:05

    Date Values Date Date


    02/20/2023 44977 2/20/2023 02
    20/02/2023 Err:502 20-Feb-2023
    02/03/2023 44960 02-Mar-2023
    03/02/2023 44987 03-Feb-2023
    03 Mar 2023 Err:502 03-Mar-2023

    Time Values Time Time


    2.00 pm 0.583333333333333 14:00:00 2:00 pm
    2.00.05 am 0.0833912037037037 02:00:05
    2:00 pm 0.583333333333333 14:00:00
    13:45:00 0.572916666666667 13:45:00
    02/03/2023 14:00 0.583333333335759 14:00:00
    8:00:02
    7:45:06 HOUR MINUTE SECOND TIME
    7:58:36 9 1 23 9:01:23
    8:01:36
    8:00:05

    23 24
    29
    20 2023
    25 26 27 28 29
    1 Insert today's date using a shortcut. Mention the shortcut that you used.

    Today's date 11/23/2023 10/5/2023


    Shortcut Key

    2 Which function can I use to get only todays date?

    3 Insert Current time using a shortcut. Mention the shortcut that you used.

    Today's time
    Shortcut Key

    4 Which function can I use to get TIME?

    5 This is a tricky question


    How do I only get today's time (without the date)
    Hint: Time function?

    6 Find your age


    Type your Date Of Birth

    Use function to calculate Age (Only in Years)

    Use function to calculate Age (Ex 36 Years, 2 Months, 4 Days)

    7 What is the last date of 3rd quarter?

    8 What is the total net working days between the below start date and end date
    Start Date 1/1/2023
    End Date 2/20/2023
    Weekend days Thursday, Friday
    List Of Holidays 1/26/2023
    2/2/2023
    2/18/2023

    Total Net Working days

    9
    Employee Name Authorized Break Time Actual Break Taken
    Anjali Ray 0:30:00 0:30:25
    Suraj Cheema 0:30:00 0:29:45
    Rajesh Rai 0:30:00 0:35:00
    Aditi Chaudhuri 0:30:00 0:30:00
    Sid Rout 0:30:00 0:28:00

    Count of employees who exceeded the break time. Use COUTNIFS and TIME
    2

    Count of employees who exceeded the break time. Use COUTNIFS and TIMEVALUE
    2

    10 This is a difficult quesiton. How much extra break time (in total) was taken.
    Hint: (Sum of exceeded break time) - (authorized break time * count of exceeded breaks)
    Expected Output 0:05:25
    0:05:25
    11/23/2023 16:54 0:02

    10/2/2023
    10/2/1996

    1/28/2022 12:00
    ceeded breaks)

    You might also like