Thank you for taking the time to read my question.
I posted this question last week then answered my own question and marked it as answered... but as it turns out I didn't really answer my own question... So I'm re-posting
I have a start date and time and an end date and time and I'd like to use DateDiff() to determine the hours:minutes:seconds of elapsed time.
I have the following that... almost works.
If I have
Start = 5:22:33
End = 5:22:43
Answer = 0:00:10 (Correct)
BUT if I have
Start = 4:15:55
End = 4:16:05
Answer = 0:01:10 (WRONG)
I can't seem to wrap my head around how to fix this...
My current formula is:
DateDiff(First(colLocHist).CaptureDateTime,Last(colLocHist).CaptureDateTime,Hours) & ":" & Text(Mod(DateDiff(First(colLocHist).CaptureDateTime,Last(colLocHist).CaptureDateTime,Minutes),60),"00") & ":" & Text(Mod(Mod(DateDiff(First(colLocHist).CaptureDateTime,Last(colLocHist).CaptureDateTime,Seconds),60),60),"00"))
Am I close? Do I need to do everything at the "Seconds" level and then calculate the minutes?
Thanks
Hi @iwonder :
I'v made a test for your reference:
1\Start and End
Set(Start,DateTimeValue("2021-5-1 4:15:55"));Set(End,DateTimeValue("2021-5-1 4:16:05"));
2\The Formula:
With(
{DiffSeconds:Text(DateDiff(Start,End,Seconds))},
RoundDown(DiffSeconds/3600,0)&
":"&
Text(RoundDown(Mod(DiffSeconds,3600)/60,0),"00")&
":"&
Mod(Mod(DiffSeconds,3600),60)
)
Best Regards,
Bof
Just had an idea. This seems to work, but is there a better way?
Text(RoundDown(RoundDown(DateDiff(First(colLocHist).CaptureDateTime,Last(colLocHist).CaptureDateTime,Seconds)/60,0)/60,0),"00") & ":" & Text(RoundDown(DateDiff(First(colLocHist).CaptureDateTime,Last(colLocHist).CaptureDateTime,Seconds)/60,0)-(RoundDown(RoundDown(DateDiff(First(colLocHist).CaptureDateTime,Last(colLocHist).CaptureDateTime,Seconds)/60,0)/60,0)*60),"00") & ":" & Text(Mod(DateDiff(First(colLocHist).CaptureDateTime,Last(colLocHist).CaptureDateTime,Seconds),60),"00")
Thanks
WarrenBelz
791
Most Valuable Professional
MS.Ragavendar
410
Super User 2025 Season 2
mmbr1606
275
Super User 2025 Season 2