Excel Date and Time: Previous date and some certain time
We have some scheduled job, but if we would like to cancel
the job, we have to make the cancellation before the previous day 3:00pm. For
example, for the job scheduled on 1/11/2017 1:00:00 PM, the cancellation before
1/10/2017 3:00:00 PM is valid. The question for us is to judge each
cancellation validation.
Step 1: Extract the date part from scheduled on column and
get one day ahead.
=DATEVALUE(MONTH(A2) & "/" &
DAY(A2-1) & "/" & YEAR(A2))
Step 2: To understand the date format better, I have to know
what the date value is in the general format. Change the format for column C to
general, and then you will see below.
Oh, yes, the dates are just some number but in date format.
Step 3: Until here, you may think about, how is 3:00:00 PM
‘s number value. It’s easy. Let’s just
type 3:00:00 PM in any empty cell, and then change it to number format. You can
see it’s 0.625.
Step 4: Let’s add the
0.625 to the dates we just got. That’ll be the number value for the previous
day 3:00:00 PM.
Step 5: Just compare the Cancelled On column with the
Scheduled On (Date and Time) column to get the final result.
Some comments:
If you want to calculate date, transfer a date from date
format to a serial number by DATEVALUE is usually helpful.
No comments:
Post a Comment