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

DATE-TIME

How excel stores time

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?

DATETIME HOURS

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

Pin It on Pinterest

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.