Convert date/time field to sortable time field

E

Erik of Wales

I have imported a date/time field from Access to Excel. I am not interested
in the date element, just the arrival hour. I am able to re-format so that
only the time shows "hh" but when this field is used in a pivot table, it
doesn't just show me the 24 hours of the day to select from, but every single
date/time.

Can anyone help? I have done a search on the forum, but could not find the
answer. Thanks.
 
V

Van T. Dinh

Use a Calculated Field / Column:

HoursOnly: Hour([DateTimeField])

instead of your DateTimeField.
 
E

Erik of Wales

Thanks for your prompt reply. Not sure I understand your message properly. I
did try and it didn't seem to work.

Basically, I have created an Arrival Hour column which references by formula
to the original date/time field and correctly pulls back the hour in "HH"
format. However, the problem is when I create a pivot table, it does not
recognise just the HH part of the "Arrival Hour" field, but also takes into
account the full DD/MM/YYYY/HH/MM. For example, I would like 1.15am and
1.20am to be grouped under arrival hour 01. Instead, in the pivot table,
these times are shown as 2 seperate selections under 01 and 01, (due to the
difference in minutes).

Van T. Dinh said:
Use a Calculated Field / Column:

HoursOnly: Hour([DateTimeField])

instead of your DateTimeField.
--
HTH
Van T. Dinh
MVP (Access)




Erik of Wales said:
I have imported a date/time field from Access to Excel. I am not interested
in the date element, just the arrival hour. I am able to re-format so that
only the time shows "hh" but when this field is used in a pivot table, it
doesn't just show me the 24 hours of the day to select from, but every single
date/time.

Can anyone help? I have done a search on the forum, but could not find the
answer. Thanks.
Click to expand...
Click to expand...
 
V

Van T. Dinh

Format _only_ alters the display of the values but underneath the values are
still _different_. Hence, when you do cross-tabing, Access / JET work on
the values, _not_ the format / display and you get different columns even
though the formatted display shows the same text.

That's why I suggested using the Calculated Column which actually calculates
the hour values, not just the formatted display. Using what I suggested,
01:15 and 01:20 will both give _value_ of 1 and therefore grouped together!
 
Top