![]() If you extract seconds then use the data type long. See the VBA help for more information about using the DateDiff function to find the difference in years, months, weeks etc. Where lSeconds is a variable of the data type Long, and Time1 and Time2 are of the data type Date. Then you must use the date function DateDiff, which needs the date too. If there are more than 24 hours between two time stamps, you can't use the methods above. 'the columns next to the date formatted times. 'Hours, minutes and seconds are inserted in 'the string variable = the cells text property. 'Extends the range to the first empty cell in the column. 'Finds the first cell in the column with date formatted times. In the following example I use it on the time stamp only. 'columns to the right of the date formatted times.ĭatePart is one of VBA's date functions, and it can extract year, date, month, week, hours, minutes and seconds from a date with a time stamp. 'Hours, minutes and seconds are inserted in the 'the data type "String", we start with a "Val" 'Because "Left$", "Mid$" and "Right$" return 'our string variable = the cell's text property. 'Because the cell contains a formula, we set ![]() Set rTime = Range(rTime, rTime.End(xlDown)) 'Expand the range to the first empty cell. 'Find the first cell in the column with date formatted times. 'Reads the date formatted times and extracts hours, minutes and If you are viewing this page on a device with a small screen, some of the code lines may appear "broken," but they will be okay, when you paste. If you want to test the code, highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module. If there is more than 24 hours between the two times you need the date too, e.g.:Īnd in that case you must use the date function DateDiff. That is turn 03:40:20 into "3", "40" and "20".įor this you can use spreadsheet functions, but VBA is ideal for such a job - especially if it is all code based.īelow I show how to extract hours, minutes and seconds by treating the time as a text string ( Method 1) or by using the date function DatePart ( Method 2). You must extract hours, minutes and seconds before you calculate. However you cannot add these times to one another. Now I got the elapsed time in hours, minutes and seconds expressed as e.g. So I typed the following formula in C2: "= 24 - A2 + B2" and copied down. In Excel you can subtract one time from another, for instance cell B2 - A2 = 03:40:20, but if you do it that way, you get a problem in row 5, where we pass midnight. (You may have to format the cells as user defined "hh:mm:ss".) I could get the times for stop and restart and typed them manually into two columns like this: ![]() A part of the plant had too many unplanned stops, and I wanted to find the total downtime for a period. ![]() The examples come from a task I had at work. If you want to test the examples, highlight the code with the mouse, copy (CTRL+C) and paste it (CTRL+V) into a VBA module. The date function DateDiff can also find the time difference between two times if they also contain a date ( Method 3). You can use VBA's string functions Left, Mid and Right ( Method 1) or the date function DatePart ( Method 2). You can also see how to find the elapsed time between two dates with times. This page shows examples of how to get hours, minutes and seconds out of a date formatted time like e.g. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |