+ Reply to Thread
Results 1 to 6 of 6

Issue with converting number to time

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Issue with converting number to time

    I have tried to advance by vba skills with a new project: Time Management.
    I have several issues with this spreadsheet. However, the one I am hoping to have guidance on is how to select the "Duration" time value in the userform and get it to show correctly. Currently, when I select 1:00 (i.e. 1 hour) it is showing as "0:00".


    Disclaimer: this worksheet is based on the videos by Randy (https://www.youtube.com/watch?v=8-IEk-pmfco. I worked on this worksheet from scratch and was doing well until the 3rd video. I have watched the videos many times but I now am stumped. If this goes against the forum rules by working off a 3rd party project, please let me know and I will delete.
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,890

    Re: Issue with converting number to time

    Quote Originally Posted by awoolfe View Post
    when I select 1:00 (i.e. 1 hour) it is showing as "0:00".
    Your list of times (Duration) is shown in units of hours, but Excel manages time in units of days. Here you say you select 1:00 but that is not what you are selecting. You are selecting 1.00 (maybe it looks like 1,00 to you, I am not sure what Australian localization looks like). That is midnight of 1 day.

    You need to change your list of times in Duration to be actual time values. I have done this for you. You can delete columns O and Q on sheet Admin. Those show how I did the conversion from hours to time.

    P.S. It doesn't matter where the file originally came from, if you have a problem we'll help.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: Issue with converting number to time

    Thank you Jeff!
    It now works. I'm glad I can get help here!!

    I have 2 more questions related to this spreadsheet.

    The easier one is how do I format the Date in the userform to UK? I tried Cdate and it looked good in the form, but still US form.

    The 2nd question related to correctly labelling the "sample" shape to the correct taskID. I this this is why an error code is coming up that shows it is out of range.
    Attached Files Attached Files

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,890

    Re: Issue with converting number to time

    OK remember that you know everything about your file and I know nothing. You are going to have to explain more about whag you are doing.

    how do I format the Date in the userform to UK? I tried Cdate and it looked good in the form, but still US form.

    I am not clear on how you are designing the user interaction for entering a date. ScDate is a textbox so you type in whatever text you want. You should not need to format the data in the userform. Also, when you do this in your code
    ScDate = Format(ScDate, "dd/mm/yyyy")
    the code won't recognize it as a date. This code doesn't actually do anything, unless you type in a data number (enter 44410 then hit Enter and you will see what I mean).

    correctly labelling the "sample" shape to the correct taskID

    No idea what you are talking about here. You'll have to walk me through it.

  5. #5
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: Issue with converting number to time

    Thanks. I see what you mean re the format date.

    The next issue is complicated.
    What should happen:
    1) In Sheet 2, when you click on "Next week", "prev week", "This week" shapes, it causes the sub "ScheduleRefresh" to run.
    2) "ScheduleRefresh" causes the shapes in Sheet 2 that are in the calendar area (column >3 and row >4) to delete.
    An autofilter of the master list of tasks in Sheet 3 A:N. The criteria of the autofilter is based on Sheet3 S2:V3 (starting date of week, end date of week, staff member and task is not pending).
    3) The filtered list is pasted to Sheet3 AA2:AN2.
    4) In Sheet 2 in the A/B columns there is a square shape named "Sample".
    5) The formula below is meant to duplicate the shape and rename it to the task ID numbers in Sheet 3 column AA.

    I am getting an error related to the code below.
    If I add On Error Resume Next, All the shapes in Sheet 2 are deleted (even the arrows).


     Sheet2.Shapes("Sample").Duplicate.Name = TaskID
                    With Sheet2.Shapes(TaskID)
                        .Left = Sheet2.Cells(ScRow, ScCol).Left 'ScRow = first row, ScCol = first Column of schedule
                        .Top = Sheet2.Cells(ScRow, ScCol).Top
                        .Width = Sheet2.Cells(ScRow, ScCol).Width
                        .Height = Range(Sheet2.Cells(ScRow, ScCol), Sheet2.Cells(ScRow + DurRows - 1, ScCol)).Height 'https://www.youtube.com/watch?v=8-IEk-pmfco 1:09
                        .TextFrame2.TextRange.Text = TaskText
                        If TaskColor <> "" Then .Fill.ForeColor.RGB = TaskColor
                    End With
    This is the section of the training where he is adding this code if I have not explained it well: https://www.youtube.com/watch?v=h79SNTpxKzc. Time: 1hr into the video.

  6. #6
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: Issue with converting number to time

    Thanks Jeff for your help!! As my question is different to my original question I will close of this one and start a new post.

+ 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. [SOLVED] Converting a text and number time into a numerical value and converting to minutes
    By RQuilley in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2020, 11:40 AM
  2. [SOLVED] Issue converting to number
    By felipemejiag in forum Excel General
    Replies: 1
    Last Post: 08-01-2014, 10:54 AM
  3. issue with converting number to date need help!
    By fredderf81 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-09-2013, 11:17 AM
  4. Converting text to time issue
    By Kamy in forum Excel General
    Replies: 2
    Last Post: 09-27-2011, 09:36 AM
  5. Issue converting string to number..
    By cummins in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-08-2011, 04:07 PM
  6. Converting Text to number issue
    By Dr Mambo in forum Excel General
    Replies: 3
    Last Post: 03-17-2009, 08:29 AM
  7. Converting Text to Number & Summing Issue
    By JumpingMattFlash in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2006, 09:23 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