DateTime Value

How to Update Date or Time in EXCEL

To update/extract the date or Time part separately in a DateTime Value is a lot easier  if you understand that excel stores dates as sequential serial numbers and time as a fractional portion of a 24 hour day.

With the above understanding, you can combine INT and MOD functions to perform calculations on or extract the date or time portions.

For example if you have Order Dates and Delivery periods (days)  you can calculate expected Delivery date and Time

EXCEL DATETIME VALUE

 

How it Works:

INT function extracts the Date portion of DateTime Value then adds the 5 days

=INT(DateTime) + Period(days)

MOD function extracts the Time portion which is the fractional part.

=MOD(DateTime,1)

What if the delivery period is in hours?

EXCEL DATETIME VALUE

NB: You need to convert the hours to fractional portion of a 24 hour for it to work thus

=MOD(DateTime,1) + (hours/24)

That’s All!

Give it a try with different days &/ Hours

Leave a Comment:

Your email address will not be published. Required fields are marked *