Day of Week

R

Robert Hampton

Good evening, all. Hoping for some help and here's what I'm trying to do:

Import a .csv file. The file can have a varied number of rows. One
column is a date. I need to convert that column to day-of-week in
numeric format (Sunday=1). I then need to export that back out to a new
..csv file with a new file name. Someone else will be using this
conversion process so it has to be as simple as possible.

The easiest way would be to use Cell Format but I can only get it to
display the day in text format (Sun or Sunday), and not numeric. I
don't know how to easily use a text or weekday formula where it would be
"hands-free" for the user.

Any suggestions? Thanks, folks!
 
P

Pete_UK

The function WEEKDAY( ) applied to a date will give you a numeric value
of 1 for Sundays. To apply this, you would need to import your file,
then add a formula like:

=WEEKDAY(D2)

in a spare column (assuming your first date starts in D2), then copy
this formula down to the extent of your data. Then you need to take the
value by highlighting the column, <copy>, Edit | Paste Special | Values
| OK then <enter>. Then you can cut/paste this column to overwrite the
dates in column D, then you can save the file with a new name.

If you don't think your User will be able to follow that, then you
could record a macro while you do it and then re-run the macro for
future imports.

Hope this helps.

Pete
 
R

Robert Hampton

Pete_UK said:
The function WEEKDAY( ) applied to a date will give you a numeric value
of 1 for Sundays. To apply this, you would need to import your file,
then add a formula like:

=WEEKDAY(D2)

in a spare column (assuming your first date starts in D2), then copy
this formula down to the extent of your data. Then you need to take the
value by highlighting the column, <copy>, Edit | Paste Special | Values
| OK then <enter>. Then you can cut/paste this column to overwrite the
dates in column D, then you can save the file with a new name.

If you don't think your User will be able to follow that, then you
could record a macro while you do it and then re-run the macro for
future imports.

Hope this helps.
Click to expand...

I've already done that. I was just hoping for something simpler and
cleaner. The biggest problem with it is not knowing how many rows will
be imported so have an long column with the formula in each cell. I'll
play with IFISBLANK maybe.

Thank you very much for your help, Pete.
 
P

Pete_UK

Thanks for the feedback.

If you already have data in the column to the left of the helper column
(where the weekday formula is) then you can copy the weekday formula
down just by double-clicking the fill handle - this will automatically
copy down to cover the number of rows you have.

Pete
 
Top