Forum Discussion
How to best Create List from Excel w/Blank Dates
The blanks in 'OutTime' still have me struggling with creating a new SP List. It also seems like I'm doing conversions on both sides of this, uneccessarily. There are times when the source Excel f...
I've managed to get Excel to place the 'InTime' in the 'OutTime' when 'OutTime' is blank; This provides a 'WaitTime' of 00:00:00, which is acceptable.
I've used the datediff you recommend before.
It returns a value that looks like the following, where the time is correct, but the date (unwanted) - in decimal, is present.
When 'OutTime' is 'InTime' - 45020.00:00:00
When 'OutTime' has a date - 45020.00:014:23
I've used the datediff you recommend before.
It returns a value that looks like the following, where the time is correct, but the date (unwanted) - in decimal, is present.
When 'OutTime' is 'InTime' - 45020.00:00:00
When 'OutTime' has a date - 45020.00:014:23
This is because the DATEDIFF function does not return a date or time value, but an integer value. To convert this integer value to a time value, you need to use another function such as DATEADD or CAST . For example, you can use the following formula to get the difference between two dates in hours as a time value:
=DATEADD(hour,DATEDIFF(hour,'OutTime','InTime'),0)
or
=CAST(DATEDIFF(hour,'OutTime','InTime') AS TIME)
These formulas will return a value that looks like 00:00:00 when 'OutTime' is 'InTime', and 14:23:00 when 'OutTime' has a date.
Hope this helps 🙂
=DATEADD(hour,DATEDIFF(hour,'OutTime','InTime'),0)
or
=CAST(DATEDIFF(hour,'OutTime','InTime') AS TIME)
These formulas will return a value that looks like 00:00:00 when 'OutTime' is 'InTime', and 14:23:00 when 'OutTime' has a date.
Hope this helps 🙂
- Do you mean I should use one of the formulas in the Excel 'WaitTime' column?
DATEADD(hour,DATEDIFF(hour,'OutTime','InTime'),0) did not work as an expression in Power Automate.