+ Reply to Thread
Results 1 to 19 of 19

Need help in dating columns

  1. #1
    Registered User
    Join Date
    02-26-2018
    Location
    Norman, OK
    MS-Off Ver
    2003
    Posts
    9

    Need help in dating columns

    I have a Excel 2003 spreadsheet in which columns need to be dated in pairs: Columns 1&2 Date 1; Columns 3&4 Date 2; Columns 5&6 Date 3, etc. Presently I have to enter dates by coping & pasting a very laborious process. I would like to find a way to make this process easier. Any help would be appreciated. I attached a sample spreadsheet.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need help in dating columns

    Hi Putz and welcome to the forum,

    I have an example BP chart. See if it makes more sense than yours.

    BP data.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-26-2018
    Location
    Norman, OK
    MS-Off Ver
    2003
    Posts
    9

    Re: Need help in dating columns

    Thanks for your reply. But my doc wants me to take my BP 2X/day. Hence the 2 columns with the same date. But your spreadsheet looks far better than mine. Care to share how you put it together? Thanks again.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need help in dating columns

    Yep,

    My table format allows you to do two times a day. Column A can be a Date and Time value showing when you took your BP. Then two numbers in columns B and C. I'd do my left and then right arm to see if my BP device was stupid. That column was the L or R for which arm I was getting the reading from.

    After you have those 3 columns you can do a Pivot Table and/or Pivot Chart like I did. I did ACE inhibitors first and got a cough so switched to ARBs (Valsartan?) which I'm on now. No cough now...

    Attach some data with twice a day readings (in 3 columns only) and we can do a Pivot and Chart with your data.

  5. #5
    Registered User
    Join Date
    02-26-2018
    Location
    Norman, OK
    MS-Off Ver
    2003
    Posts
    9

    Re: Need help in dating columns

    Good to hear from you again.

    Do you enter the dates as you enter your BP? One of the things that make entering the dates so tedious for me is I enter all the dates on a page at the same time—copy & pasting over & over.

    I really like your chart but I’d have a lot to learn to replicate it for myself. Thanks for your willingness to look at my chart. I’ve attached a page. The data are older because I damaged my left shoulder which had been replaced before & was re-replaced last November. I didn’t take my BP for awhile because I take it on my left arm & it was too painful to attach the cuff, plus with the pain from my shoulder my BP was very high.

    The page I attached shows the data in rows. You had mentioned columns. If you need the data in columns I can redo the page.

    BTW I use an Omron BP meter. I really like the molded cuff. For what its worth I used to take Avapro (angiotensan II antagonist) & Norvasc (calcium channel blocker). Now I take Losartan (ARB) & Terazosin (alpha adrenergic blocker). Strokes run in my family & I’d just as soon not have one.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Need help in dating columns

    Perhaps this will be of help in converting the existing data into columns:
    1) In column A fill in the first date twice then the rest of the values are populated using: =IF(ISEVEN(ROW()),A3+1,A3)
    2) In column B the values are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3) In column C the values are populated using: =SUMPRODUCT(('Page 7'!B$5:K$57=A2)*('Page 7'!B$6:K$58=B2)*('Page 7'!B$7:K$59))
    4) In column D the values are populated using: =SUMPRODUCT(('Page 7'!B$5:K$57=A2)*('Page 7'!B$6:K$58=B2)*('Page 7'!B$8:K$60))
    5) Cell G1 displays the row to which the formulas need to be copied, itself using a formula: =('Page 7'!K57-'Page 7'!B5+1)*2+1
    Note: Originally on the page 7 tab both readings for 8/1 showed AM and both readings for 9/7 showed PM
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    02-26-2018
    Location
    Norman, OK
    MS-Off Ver
    2003
    Posts
    9

    Re: Need help in dating columns

    Thanks for your reply. It will take me a couple days to process your solution. I hope you won't mind answering any questions I might have. Thanks for catching my errors of 8/1 & 9/7. I'm sure I made plenty of others. 2017 was a nightmarish year for me. The worst year of my life. I was amazed that my BP did as well as it did. Thanks again.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Need help in dating columns

    You're Welcome and thank you for the feedback. My purpose in pointing out 8/1 and 9/7 was to draw attention to my having changed those so that the fist of each pair is AM and the second is PM, hopefully that is correct. I hope that you have a blessed day.

  9. #9
    Registered User
    Join Date
    02-26-2018
    Location
    Norman, OK
    MS-Off Ver
    2003
    Posts
    9

    Re: Need help in dating columns

    I wanted to thank you again for your reply but I have not been able to emulate it. I placed my Page 7 into a new worksheet. Added another page to the worksheet. Entered the column headings into the new page (Date, Time, etc). Entered the date twice then copied your formula - =IF(ISEVEN(ROW()),A3+1,A3) - into the next row (A4). The result was #NAME? The same thing happened with the other columns using your various formulas. I don't know enough about Excel to troubleshoot the problem. I appreciate your help but I think I will stick to the format of my original worksheet. I can get more data per page with that format. Which returns me to the problem as stated in my original post about this issue. Which I'll repeat here:

    I have a Excel 2003 spreadsheet in which columns need to be dated in pairs: Columns 1&2 Date 1; Columns 3&4 Date 2; Columns 5&6 Date 3, etc. Presently I have to enter dates by coping & pasting a very laborious process. I would like to find a way to make this process easier. Any help would be appreciated. I attached a sample spreadsheet.


    If you could offer any help on that problem I'd appreciate it.
    Attached Files Attached Files

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need help in dating columns

    Hi Putz,

    I'm sorry we haven't convinced you to change the layout of your worksheet. Perhaps a read of:
    https://www.thoughtco.com/create-dat...l-2003-3123445 will help.

    Very specifically, Excel works with rows and columns of data. You should (In my opinion) have a single row for each time you take your BP. The columns at the top of these rows should be: Date, Time, Systolic, Diastolic, Pulse.

    When you arrange your dates in columns across to the right, it breaks this Table format and reduces what Excel can do for you. I wanted my example to show you how to arrange your collection of data. Please reconsider as I'd like you to succeed in collecting data and finding the beauty of Excel Lists and Tables.

  11. #11
    Registered User
    Join Date
    02-26-2018
    Location
    Norman, OK
    MS-Off Ver
    2003
    Posts
    9

    Re: Need help in dating columns

    Hi MarvinP:

    Good to hear from you. You haven’t failed to convince me to change the layout of my worksheet.  I am very amiable & willing to be convinced to change if the change meets my goal. My goal in these BP sheets was to show my doc the results in an easy to read format. That should probably include a line chart but life interrupted before I got to that.

    My goals in changing to a columnar layout would be to retain my existing data without having to retype it-ugh. JeteMc suggested a way to change my data to columns but I wasn’t able to replicate his solution because I am too ignorant of Excel. Other goals would be to propagate the date column showing duplicate dates in succeeding rows. I could type this in as I go because I have to type in the time as I go anyway. I would like to pack as much data/page to make it easy for the doc to visually scan it. And I would like to develop a line chart which might eliminate my goal of packing each sheet with data.

    I appreciate the link to the ThoughCo.com article. That will be very useful.

    Thank you again for your reply. Do you have any thoughts on my goals?

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Need help in dating columns

    Quote Originally Posted by Putz View Post
    Do you have any thoughts on my goals?
    Weren't you given some direction already? You seem to be making things harder than they have to be...

    change the layout of your worksheet
    HTH
    Regards, Jeff

  13. #13
    Registered User
    Join Date
    02-26-2018
    Location
    Norman, OK
    MS-Off Ver
    2003
    Posts
    9

    Re: Need help in dating columns

    Thanks for your thoughts. I don't know what direction you think I have received. Why do you think I'm trying to make things harder than they have to be? I have several pages of data dating back to 2015. I don't really want to retype all of it.

  14. #14
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need help in dating columns

    Hi Putz,

    Attach your data and we'll convert it for you.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Need help in dating columns

    Putz, the problem was entirely mine. I overlooked the '2003' in your profile, and the ISEVEN function was apparently not added until the2007 version. I believe that MOD was available to the 2003 version, and if so the following should work:
    1) In A4 and down: =IF(MOD(ROW(),2)=0,A3+1,A3)
    2) In B2 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions or problems.
    Attached Files Attached Files

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Need help in dating columns

    Using you attachment in post #5, here's a macro to convert your data into a format better suited for analysis.

    If your spreadsheet should be slightly different, we can make the necessary adjustments.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    02-26-2018
    Location
    Norman, OK
    MS-Off Ver
    2003
    Posts
    9

    Re: Need help in dating columns

    Hi MarvinP:



    I really appreciate your offer to convert my data. I didn’t expect that. But I’ll take you up on your offer. I’m attaching the spreadsheet. Thank you for your help.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    02-26-2018
    Location
    Norman, OK
    MS-Off Ver
    2003
    Posts
    9

    Re: Need help in dating columns

    Hi:

    Thanks for your kind offer. I tried posting a response in 2 different ways including sending you my data but I don't see either one in this thread. I hope you received my response. Please let me knoow. Thanks

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Need help in dating columns

    The data on the seven pages has been copy and pasted onto page 1 and is then converted into columns, using formulas similar to those presented earlier, on sheet1.
    Conditional formatting was added to to column B so that zero values, which otherwise would have shown as 12:00 AM, are hidden.
    A pivot table has been added for demonstration purposes, however the values look reasonable to me.
    Let us know if you have any questions.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automatic up-dating
    By shawint in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-30-2014, 05:49 AM
  2. Semi Monthly Dating
    By Mathalete in forum Excel General
    Replies: 7
    Last Post: 07-15-2011, 03:28 AM
  3. Automatically up-dating charts.
    By Steve-B in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-12-2009, 12:34 PM
  4. Looping through sheets and dating
    By T De Villiers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2007, 09:11 AM
  5. Dating cells in a worksheet using VBA
    By FLYNNE in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2006, 08:55 AM
  6. Dating Problem
    By Gary's Student in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-21-2005, 08:05 AM
  7. dating problems!
    By Jose Mourinho in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-12-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1