Using DateAdd, cannot show time portion when time result is 00:00:00
If I DateAdd a number of minutes which happens to land me on 00:00:00 portion of a date, I CANNOT seem to get my date variable to include the zeros. OK, it's really there, but I need to see/extract the portion later. Without it, testing the rest of the code is problematic since there's no guarantee the resulting date time calculation will be accurate.
If I call Function testExpression(dteStart As String, lngLenStart As Long, lngLenEnd As Long, lngRate As Long) As String
as testExpression(#1/8/2019 8:00:00 AM#,48,20,16)
The calculation looks like
dteEnd = DateAdd("n", ((48 - 20) * 16 * 60), dteStart) which just happens to land me on 01/27/2019 00:00:00, which is bizarre enough. To show the full date/time for this date/time, I have tried
- variable as a Date type and as a string - no difference
- format function
dteEnd = Format(DateAdd("n", (lngLenStart - lngLenEnd) * lngRate * 60, dteStart), "mm/dd/yyyy hh:nn:ss") which shows 1/27/19 [no time portion]
- FormatDateTime function as in (subbed numbers to shorten expression)
dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 0)
dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 1)
dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 2) all which show 1/27/19
dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 3)
dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 4) both showing 12:00:00 AM
For my purposes, I suppose I'll be able to use the separated values as dteTime (e.g. 1/27/19) and dteHour but I might need to convert dteHour to a string to get leading zeros, but here's the question (finally):
- why the #$LL does time portion not show when it's really there when using any of those formatting attempts? All I have to do is change a variable to move me off of that date/time and it displays as it should (e.g. change 60 to 61, result is 1/27/19 7:28:00 AM )
After more than 3 hours, I'm stumped and will have to do a work around if there isn't a simple solution, such as split the date and time portions. I didn't post the whole function as it's a mess right now and I don't think it's the issue - save for possibly my use of any format functions.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.