I would like to round up dates in Excel 1

[litho]

I have sveral sets of data in Excel that I am trying to corrleate by date, but the dates are not the same for the different sets of data, so my plan is to round the date to the nearest hour then I should be able to do a pairwise correlation.

So if I have a date & time in Excel format how can I round it to say teh nearest hour..??

1/2/02 8:15 -> 1/2/02 8:00
1/2/02 8:55-> 1/2/02 9:00

etc

 

[acron]

An interesting question.

If your date is in A1, the following formula will provide a rounded time, in his case to the nearest 60 minutes. Changing the 60 to 30, will round to the nearest half hour.

=INT(A11)+TIME(HOUR(A11),MROUND(MINUTE(A11),60),0)

Hope that helps.

 

[acron]

I omitted to mention that you need to have the analysis toolpak installed and active for the MROUND function to work.

If that causes a problem post back and we might come up with an alternative.

AC