+ Reply to Thread
Results 1 to 9 of 9

Time stamp with condition that row A1:A6 are input

  1. #1
    Registered User
    Join Date
    11-02-2018
    Location
    SoCal
    MS-Off Ver
    2010
    Posts
    16

    Time stamp with condition that row A1:A6 are input

    I'm trying to do a VBA that will input a TIME STAMP when ROW1's (someone said "accross the columns") input cells is all filled with data.
    I refer to ROW because I need to verify at what time was each day's cleaning complete--RmA:RmD were completed at what time..
    I made a simple excel to illustrate.

    ONE row is just a Date and rooms A through E
    Below these are values: Date is filled, and whether each room was clean.
    If all of the cells are filled in, I would like a times stamp to see what time it was complete.


    Date Clean RmA Clean RmB Clean RmC Clean RmD Clean RmE Time Completed
    22-May Yes Yes N/A Yes Yes TIME STAMP!
    23-May Yes Yes Yes (NO TIME STAMP)

    Thanks,
    Last edited by Betta-J; 05-24-2022 at 06:34 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Time stamp with condition that row A1:A6 are input

    A little confusing without sample workbook. And your thread title suggests the data by rows, but your sample data is across columns.

    Anyway try this code in the Worksheet Object and see if it works:
    Please Login or Register  to view this content.
    Note that the logic will put the timestamp if all columns have values. If any of the data is changed and the timestamp already exists, it won't update the timestamp. If any of the data is removed, the timestamp will be removed.

    Let us know if any of that logic isn't what you are intending and we can update.
    <<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts

  3. #3
    Registered User
    Join Date
    11-02-2018
    Location
    SoCal
    MS-Off Ver
    2010
    Posts
    16

    Re: Time stamp with condition that row A1:A6 are input

    added file...i think
    Attached Files Attached Files
    Last edited by Betta-J; 05-24-2022 at 06:40 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Time stamp with condition that row A1:A6 are input

    So in your new example, there are just four rooms, so we adjust the ranges from yesterday's VBA code from B:F to B:E. Some other minor code edits too.

    Just to confirm, it makes no difference if the value is Yes, No or N/A - as long as the cell isn't blank? If that's not what you intend we need to amend the code slightly.

    Please Login or Register  to view this content.
    Again, just want to check the following behaviour is what you are looking for:
    - If any of the data is changed (e.g. all cells are filled but a 'N/A' changes to a 'Yes') and the timestamp already exists, it won't update the timestamp.
    - If any of the data is subsequently deleted, i.e. not all cells will have values, the timestamp will be removed.

  5. #5
    Registered User
    Join Date
    11-02-2018
    Location
    SoCal
    MS-Off Ver
    2010
    Posts
    16

    Re: Time stamp with condition that row A1:A6 are input

    Yes, this works!
    However, I do not need the date, only the time. Do I just delete the word "date" from the

    If dataCount = countOfRooms And dateTimeLocation.Value = 0 Then dateTimeLocation.Value = Now
    If dataCount < countOfRooms Then dateTimeLocation.ClearContents

    Thanks!
    Last edited by Betta-J; 05-25-2022 at 04:21 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Time stamp with condition that row A1:A6 are input

    Great that it works.

    To remove the date - not exactly. dateTimeLocation is a made up name used to store the location of a cell. The bit you want to change is '= Now' to '= Time()'

    I've changed the code slightly to also provide some number formatting to the time, so it only shows hours:minutes rather than hours:minutes:seconds. If you want it to appear differently, show me the preferred output format and we can easily adjust.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-02-2018
    Location
    SoCal
    MS-Off Ver
    2010
    Posts
    16

    Re: Time stamp with condition that row A1:A6 are input

    Wow, this is turning out exactly the way I wanted!

    I'm ok with the hh:mm and no seconds; I'll probably just change to military time through cell format.

    I do have a question about the VBA...
    When the conditions are met and the time shows up, it's about 2 hours less than the current time...
    I looked at the formula and I found the Time() area with a -2 /24...wondering if that has to do with it?

    .Value = Time() - 2 / 24

    Thanks,

  8. #8
    Registered User
    Join Date
    11-02-2018
    Location
    SoCal
    MS-Off Ver
    2010
    Posts
    16

    Re: Time stamp with condition that row A1:A6 are input

    I just fooled around with the formula and changed from
    .Value = Time() - 2 / 24

    to
    .Value = Time() - 0 / 24

    and it worked...hope that was correct. thx.

  9. #9
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Time stamp with condition that row A1:A6 are input

    No problem, happy to help.

    The '-2' was just for my testing to check it was formatting the time correctly and forgot to take it out. But the change is absolutely correct (although you can drop the - 0/24 all together as its now redundant)

    Revised code, and with cell formatting set to military:

    Please Login or Register  to view this content.

+ 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. Time Stamp Active Cells and Record a History of Time Stamp and Active Cell Value
    By harishangaran in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-22-2017, 12:41 PM
  2. Computer time stamp -> excel time stamp
    By j.petrut in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-27-2014, 05:49 PM
  3. How to get time stamp and user name stamp
    By eskains in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-07-2013, 09:44 AM
  4. Replies: 1
    Last Post: 04-25-2013, 12:17 PM
  5. Replies: 3
    Last Post: 11-06-2012, 01:37 AM
  6. User to input character "A", but cell will show time stamp, and lock the cell.
    By matrix1108 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-09-2012, 01:02 PM
  7. Time stamp - data input to row
    By george-v in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2006, 04:50 AM

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