RodL--Thank you very much for your solution. After trying it out, I realized your logic was mostly sound but wasn't quite fool-proof since I still got times that were off by 1 sec occasionally. I used your thought process to sketch out a full, fool-proof solution that got me the right answer 100% of the time for the full date & time.
Step 1 - Create the base set of data to work with (all "double" data types):
- DAY_DECIMAL: [ExcelDateTimeVal]
- DAY_ROUNDDOWN: FLOOR([ExcelDateTimeVal])
- TIME_DECIMAL: [ExcelDateTimeVal] - FLOOR([ExcelDateTimeVal])
- HR_DECIMAL: [TIME_DECIMAL] * 24
- HR_ROUNDDOWN: FLOOR([HR_DECIMAL])
- MIN_DECIMAL: ([HR_DECIMAL] - [HR_ROUNDDOWN]) * 60
- MIN_ROUNDDOWN: FLOOR([MIN_DECIMAL])
- SEC_DECIMAL: ([MIN_DECIMAL] - [MIN_ROUNDDOWN]) * 60
- SEC_ROUNDDOWN: FLOOR([SEC_DECIMAL])
- SEC_ROUND: ROUND([SEC_DECIMAL], 1)
Step 2 - Resolve the correct date & time parts one at a time using only the base data set (doing this eliminates having to perform the steps in any particular order):
- SEC_RESOLVED: IIF([SEC_ROUND]=60,0,[SEC_ROUND])
- MIN_RESOLVED: IIF(IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN])=60, 0, IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN]))
- HR_RESOLVED: IIF(IIF(IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN])=60,[HR_ROUNDDOWN]+1,[HR_ROUNDDOWN])=24, 0, IIF(IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN])=60,[HR_ROUNDDOWN]+1,[HR_ROUNDDOWN]))
- DAY_RESOLVED: IIF(IIF(IIF([SEC_ROUND]=60,[MIN_ROUNDDOWN]+1,[MIN_ROUNDDOWN])=60,[HR_ROUNDDOWN]+1,[HR_ROUNDDOWN])=24, [DAY_ROUNDDOWN]+1, [DAY_ROUNDDOWN])
Step 3 - Resolve the date portion of the date & time:
- DATE_RESOLVED: DateTimeAdd("1900-01-01 00:00:00",[DAY_RESOLVED]-2,"days")
Step 4 - Resolve the full date and time using [DATE_RESOLVED] created in Step 3:
- DATETIME_RESOLVED: DateTimeAdd(DateTimeAdd(DateTimeAdd([DATE_RESOLVED],[HR_RESOLVED],"hours"),[MIN_RESOLVED],"minutes"),[SEC_RESOLVED],"seconds")
