Issue with converting number to time

[awoolfe]

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.

[6StringJazzer]

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.

[awoolfe]

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.

[6StringJazzer]

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

Please Login or Register  to view this content.

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.

[awoolfe]

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).

Please Login or Register  to view this content.

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.

[awoolfe]

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.