Filling-in dates and times in Excel Spreadsheet

[Jack]

Hi all,
I have an access query that I export to Excel. There are
two columns in the Excel spreadsheet that I want to fill-in
with days (Day) and day times (Time) based on the value of
another column (Date)

Ex: Date Day Time
11/3/03 Monday 10:00 am
11/3/03 Monday 10:30 am
11/3/03 Monady 11:00 am
11/4/03 Tuesday 10:00 am
11/4/03 Tuesday 10:30 am

And so forth to fill-in all correspondent days and times
for the entire month
Please help.
Thank You.

 

[John Nurick]

Hi Jack,

If you want to create these values as fields in the Access query you can
do it with expressions like this:

DayOfWeek: Format([TheDate],"dddd")
TimeOfDay: Format([TheDate],"hh:nn am/pm")

By the way, it's a good idea never to use names like "Date", "Name" and
so on for fields, because they are also common names for properties etc.
in Access, and sooner or later either you or the computer will get
confused.

There are worksheet functions in Excel that do the same job, though thye
don't always have the same names.

 

[Jack]

John,
Thank you for your reply. An expression like Format
([TheDate],"dddd") and the other one for the Time will
fill-in the days and the times??
My problem is not formating the fields, but filling-in the
days and the times for the dates. To be more specific I'm
looking for a way to automate a schedule, the way if one
date exists more than once- ex:11/3/03 exists three times,
I want the query to generate three correspondents records
like the following:

Date Schedule Day Schedule Time
11/3/03 Monday 10:00 am
11/3/03 Monday 10:30 am
11/3/03 Monday 11:00 am

And so on for all (Dates) of the month - I have the (Date)
field in my database-, I want to generate correspondent
(Schedule days) and (Schedule Times)-I don't have these
two fields and I want them to be generated automatically.

Please Advise. Thanks for your help.

-----Original Message-----
Hi Jack,

If you want to create these values as fields in the Access query you can
do it with expressions like this:

DayOfWeek: Format([TheDate],"dddd")
TimeOfDay: Format([TheDate],"hh:nn am/pm")

By the way, it's a good idea never to use names like "Date", "Name" and
so on for fields, because they are also common names for properties etc.
in Access, and sooner or later either you or the computer will get
confused.

There are worksheet functions in Excel that do the same job, though thye
don't always have the same names.

Hi all,
I have an access query that I export to Excel. There are
two columns in the Excel spreadsheet that I want to fill- in
with days (Day) and day times (Time) based on the value of
another column (Date)

Ex: Date Day Time
11/3/03 Monday 10:00 am
11/3/03 Monday 10:30 am
11/3/03 Monady 11:00 am
11/4/03 Tuesday 10:00 am
11/4/03 Tuesday 10:30 am

And so forth to fill-in all correspondent days and times
for the entire month
Please help.
Thank You.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Hi Jack,

If I understand you right, it's possible to do this either with a
cunning query in Access (too cunning for me<g>) or with cunning formulas
in Excel. Depending which way you want to go, I suggest you post either
to the Access Queries newsgroup or to an Excel group to get more
specific help.

One thing people may want to know: if you have three records for the
same day, how is the computer to decide what order they should be
scheduled in?

John,
Thank you for your reply. An expression like Format
([TheDate],"dddd") and the other one for the Time will
fill-in the days and the times??
My problem is not formating the fields, but filling-in the
days and the times for the dates. To be more specific I'm
looking for a way to automate a schedule, the way if one
date exists more than once- ex:11/3/03 exists three times,
I want the query to generate three correspondents records
like the following:

Date Schedule Day Schedule Time
11/3/03 Monday 10:00 am
11/3/03 Monday 10:30 am
11/3/03 Monday 11:00 am

And so on for all (Dates) of the month - I have the (Date)
field in my database-, I want to generate correspondent
(Schedule days) and (Schedule Times)-I don't have these
two fields and I want them to be generated automatically.

Please Advise. Thanks for your help.

-----Original Message-----
Hi Jack,

If you want to create these values as fields in the Access query you can
do it with expressions like this:

DayOfWeek: Format([TheDate],"dddd")
TimeOfDay: Format([TheDate],"hh:nn am/pm")

By the way, it's a good idea never to use names like "Date", "Name" and
so on for fields, because they are also common names for properties etc.
in Access, and sooner or later either you or the computer will get
confused.

There are worksheet functions in Excel that do the same job, though thye
don't always have the same names.

Hi all,
I have an access query that I export to Excel. There are
two columns in the Excel spreadsheet that I want to fill- in
with days (Day) and day times (Time) based on the value of
another column (Date)

Ex: Date Day Time
11/3/03 Monday 10:00 am
11/3/03 Monday 10:30 am
11/3/03 Monady 11:00 am
11/4/03 Tuesday 10:00 am
11/4/03 Tuesday 10:30 am

And so forth to fill-in all correspondent days and times
for the entire month
Please help.
Thank You.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.