exceljet.net Open in urlscan Pro
2600:3c00::f03c:91ff:fee7:ea6e  Public Scan

Form analysis
4 forms found in the DOM

GET /search

<form class="search-form" action="/search" method="get" id="header-search" accept-charset="UTF-8">
  <input type="text" name="keys" value="" placeholder="Search" size="15" maxlength="128" class="form-text search-text">
  <input type="submit" name="op" value="Search" class="form-submit search-submit">
</form>

GET /search

<form class="search-form" action="/search" method="get" id="header-search" accept-charset="UTF-8">
  <input type="text" name="keys" value="" placeholder="Search" size="15" maxlength="128" class="form-text search-text">
  <input type="submit" name="op" value="Search" class="form-submit search-submit">
</form>

POST /antibot

<form class="webform-client-form-3946 webform-client-form webform-client-form webform-client-form-3946 antibot" action="/antibot" method="post" id="webform-client-form-3946" accept-charset="UTF-8">
  <div>
    <div class="webform-progressbar">
    </div>
    <div class="form-item webform-component webform-component-textfield webform-component--name">
      <label for="edit-submitted-name">Name </label>
      <input placeholder="Your name" type="text" id="edit-submitted-name" name="submitted[name]" value="" size="60" maxlength="128" class="form-text">
    </div>
    <div class="form-item webform-component webform-component-email webform-component--email">
      <label for="edit-submitted-email">Email </label>
      <input class="email form-text form-email" placeholder="email@example.com" type="email" id="edit-submitted-email" name="submitted[email]" size="60">
    </div>
    <div class="form-item webform-component webform-component-textarea webform-component--problem">
      <label for="edit-submitted-problem">Problem <abbr class="form-required" title="This field is required.">*</abbr></label>
      <div class="form-textarea-wrapper resizable"><textarea required="required" placeholder="Describe the problem" id="edit-submitted-problem" name="submitted[problem]" rows="5" cols="60" class="form-textarea required resize-vertical"></textarea>
      </div>
    </div>
    <div class="form-item webform-component webform-component-markup webform-component--email-message">
      <p>Your email address is private and not shared.</p>
    </div>
    <input type="hidden" name="details[sid]">
    <input type="hidden" name="details[page_num]" value="1">
    <input type="hidden" name="details[page_count]" value="1">
    <input type="hidden" name="details[finished]" value="0">
    <input type="hidden" name="form_build_id" value="form-9QIdXyDlyISUb-bB81A0nFwzSnuAbZojUXk0pALNQLs">
    <input type="hidden" name="form_id" value="webform_client_form_3946">
    <input type="hidden" name="antibot_key" value="">
    <div class="form-actions"><input class="webform-submit button-primary button-primary form-submit ajax-processed" type="submit" id="edit-submit" name="op" value="Submit"></div>
  </div>
</form>

POST https://sendy.exceljet.net/subscribe

<form id="newsletter_signup_footer" class="newsletter-signup-form newsletter-signup-footer-form" action="https://sendy.exceljet.net/subscribe" method="POST" accept-charset="utf-8">
  <label for="email" hidden="">Email</label>
  <input type="email" name="email" placeholder="email address" id="newsletter-signup-footer-email" class="newsletter-signup-footer-email newsletter-signup-email text-input" required="">
  <div style="display:none;">
    <label for="hp">HP</label>
    <input type="text" name="hp" id="newsletter-signup-footer-hp">
  </div>
  <input type="hidden" name="SignupLocation" id="newsletter-signup-footer-SignupLocation" value="footer">
  <input type="hidden" name="list" value="T1JX0z264YewQdKf7gXIig">
  <input type="hidden" name="subform" value="yes">
  <input type="submit" name="submit" id="newsletter-signup-footer-submit" class="newsletter-signup-footer-submit form-submit" value="Subscribe">
</form>

Text Content

Skip to main content
 * Login

Cart
Exceljet
 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles


Cart
 * Login

Main menu toggle
 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles

 * Login




YOU ARE HERE

Home › Formulas › Extract time from a date and time
 * Previous
 * Next

Date and Time


EXTRACT TIME FROM A DATE AND TIME


RELATED FUNCTIONS 

MOD



SUMMARY

To extract the time from a date that contains time (sometimes called a
"datetime"), you can use a formula based on the MOD function. In the example
shown, the formula in D5 is:

=MOD(B5,1)

which returns the time portion of the value in B5, 1:00 PM.

Powered By

10



How to use VLOOKUP with a Table


Share


Next
Stay






GENERIC FORMULA

=MOD(A1,1)


EXPLANATION 

Excel handles dates and times using a system in which dates are serial numbers
and times are fractional values. For example, June 1, 2000 12:00 PM is
represented in Excel as the number 36678.5, where 36678 is the date (June 1,
2000) and .5 is the time (12:00 PM). In other words, the time value in a
"datetime" is the decimal.

The MOD function returns the remainder from division. The first argument is the
number and the second is the divisor. Here are a few examples:

=MOD(5,2) // returns 1
=MOD(7,5) // returns 2

If you use MOD with a divisor of 1, the result will be the decimal part of the
number, if any, because every whole number can be evenly divided by itself. For
example:

=MOD(3.125,1) // returns 0.125

In short, =MOD(number,1) returns just the fractional part of a number,
discarding the integer portion, so it's a convenient way to extract time from a
date.

Note: if you use this formula to strip the time from a date + time, you'll need
to apply a suitable number format to display as time.


RELATED FORMULAS


EXTRACT DATE FROM A DATE AND TIME

Excel handles dates and time using a scheme in which dates are serial numbers
and times are fractional values . For example, June 1, 2000 12:00 PM is
represented in Excel as the number 36678.5, where 36678 is the date portion and
.5 is the time portion. If you have dates that include time, you can...


ROUND TIME TO NEAREST 15 MINUTES

MROUND rounds to nearest values based on a supplied multiple. When you supply
"0:15" as the multiple, Excel internal converts 0:15 into 0.0104166666666667,
which is the decimal value that represents 15 minutes, and rounds using that
value. You can also express 15 minutes in a formula with this...


GROUP TIMES INTO 3 HOUR BUCKETS

If you need to group times into buckets (i.e. group by 6 hours, group by 3
hours, etc.) you can do so with a rounding function called FLOOR. In the example
shown, we have a number of transactions, each with a timestamp. Let's say you
want to group these transactions into buckets of 3 hours like...


RELATED FUNCTIONS


MOD FUNCTION

The Excel MOD function returns the remainder of two numbers after division.  For
example, MOD(10,3) = 1. The result of MOD carries the same sign as the divisor.



Author


DAVE BRUNS

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help
you work faster in Excel. We create short videos, and clear examples of
formulas, functions, pivot tables, conditional formatting, and charts.


RELATED INFORMATION


FORMULAS

 * Extract date from a date and time
 * Round time to nearest 15 minutes
 * Group times into 3 hour buckets


FUNCTIONS

 * MOD Function


LINKS

 * http://excelsemipro.com/2012/11/extract-time-with-the-mod-function-in-excel/


TRAINING

 * Core Formula


DOWNLOAD 100+ IMPORTANT EXCEL FUNCTIONS

Get PDF Guide


TOPICS

 * Formula Basics
 * Formula Examples
 * Conditional Formatting
 * Pivot Tables
 * Excel Tables
 * Excel Charts
 * Shortcuts
 * More...


KEY FUNCTIONS

 * IF function
 * VLOOKUP function
 * XLOOKUP function
 * FILTER function
 * SUMIFS function
 * COUNTIFS function
 * SUMPRODUCT function
 * INDEX and MATCH
 * 350 Excel Functions


I think your website is awesome! Thanks so much for all the new things you have
taught me. I’m semi-retired but still love finding Excel solutions.
Ann
More Testimonials


GET TRAINING


QUICK, CLEAN, AND TO THE POINT TRAINING

Learn Excel with high quality video training. Our videos are quick, clean, and
to the point, so you can learn Excel in less time, and easily review key topics
when needed. Each video comes with its own practice worksheet.

View Paid Training & Bundles

Report a problem with this page


HELP US IMPROVE EXCELJET

You must have JavaScript enabled to use this form.
Name
Email
Problem *


Your email address is private and not shared.


Exceljet
Quick, clean, and to the point


RESOURCES

 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles


ABOUT US

 * About
 * Testimonials
 * Topics
 * Contact
 * Donate


NEWSLETTER SIGN-UP

Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
Email
HP
© 2012-2024 Exceljet. Terms of use
 * Visit our Twitter page