➕

Date and Time Functions (TODAY, DATEDIF, etc.)

When it is difficult to manage schedules by date:

• When you need to calculate the period between the project start date and the end date.

When processing time data:

• When calculating working hours or filtering data after a specific time.

Solution:

1. TODAY Function: Returns the current date

• Problem: Calculate the remaining period until the task deadline based on the current date.

• Usage:

  =TODAY()
  
  

  Returns today's date.

• Application Tip: You can subtract TODAY() from another date to calculate the remaining days.

2. NOW Function: Returns the current date and time

• Problem: When you need to check the time and date in real-time.

• Usage:

  =NOW()
  
  

  Returns the current date and time.

• Application Tip: When using for time calculations, ignore seconds.

3. DATEDIF Function: Calculates the difference between two dates

• Problem: When you need to calculate the project duration.

• Usage:

  =DATEDIF(A1, B1, "d")
  
  

  Returns the difference between A1 and B1 in days.

• Options:
  • "d" (days)
  • "m" (months)
  • "y" (years)

4. TEXT Function: Changing the date and time format

• Problem: Displaying date data in the desired format.

• Usage:

  =TEXT(A1, "yyyy-mm-dd")
  
  

  Displays the date in "yyyy-mm-dd" format.

• Application Tip: Utilize custom formats to generate various outputs.

Below are explanations and real-world examples of the TODAY, NOW, DATEDIF, and TEXT functions, using sample data that includes input values and results.

Table: Project Schedule

1. TODAY Function: Returns the current date

Example:

• Problem: Calculate the remaining days until the deadline for each project based on today's date.

• Usage:

  =TODAY()
  
  

  • Result: Today's date (e.g., 2024-12-19).

Additional Calculation:

• Calculating Remaining Days:

  =B2 - TODAY()
  
  

  • Result: The value obtained by subtracting today's date from the end date.

2. NOW Function: Returns the current date and time

Example:

• Problem: Display the current date and time.

• Usage:

  =NOW()
  
  

  • Result: 2024-12-19 14:35:21 (Real-time data).

Additional Application:

• Calculating Remaining Time:

  =B2 - NOW()
  
  

  • Result: The remaining time until the end date.

3. DATEDIF Function: Calculates the difference between two dates

Example:

• Problem: Calculate project duration in days, months, or years.

• Usage:

  =DATEDIF(A2, B2, "d")
  
  

  • Result: Returns the difference between two dates in days.

Additional Options:

• Calculate in months:

  =DATEDIF(A2, B2, "m")
  
  

• Calculate in years:

  =DATEDIF(A2, B2, "y")
  
  

Updated Table with Duration

4. TEXT Function: Changing the date and time format

Example:

• Problem: Display date data in a custom format.

• Usage:

  =TEXT(A2, "yyyy년 mm월 dd일")
  
  

  • Result: "2024년 01월 01일"

Application:

• Display only month and year:

  =TEXT(A2, "yyyy-mm")
  
  

  • Result: "2024-01"

• Format output as "Project A - End Date":

  ="Project A - " & TEXT(B2, "yyyy년 mm월 dd일")
  
  

  • Result: "Project A - 2024년 03월 31일"

Additional Applications

1. Automatically Display Project Status

• Problem: Automatically determine whether a project is completed or in progress.

• Usage:

  =IF(TODAY() > B2, "Completed", "In Progress")
  
  

2. Calculate Days Until the Next Project Start Date

• Problem: Calculate the number of days until the next project start date based on today’s date.

• Usage:

  =A3 - TODAY()
  
  

  • Result: Days remaining until the next project (negative values indicate already started projects).

Additional Functions

WORKDAY

• Calculates the date after a specified number of working days from the start date.
  • Example:

    =WORKDAY(TODAY(), 10)
    
    

  • Result: The workday that falls 10 days after today.

NETWORKDAYS

• Calculates the number of working days between two dates.
  • Example:

    =NETWORKDAYS(A1, B1)
    
    

EOMONTH

• Calculates the last date of a specific month.
  • Example:

    =EOMONTH(A1, 0)