Calculate difference between dates and times - display as percent elapsed

[Sparkytech]

I have a sheet with start and end dates and times, like this: 02/20/2018 12:00. These days and times span many days. I currently have it calculating the difference between these dates and times, and displaying that time as “Duration” in Days / Hours. I would like it to calculate half hour increments. (My start and end times will always be hours or half hours)

My formula is this:
HTML Code:

Please Login or Register  to view this content.

It displays 3 Days 2 Hours, but I would like it to show 3 Days 2.5hours, as the INT is rounding down the hours.

I would also like to be able to calculate the percentage time elapsed between the start date / time, and the current date / time.

Thanks in advance for the assistance!

[sandy666]

Attach a sample workbook (not a picture). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[MrShorty]

My first thought is why are you converting good date/time serial numbers to text? I think this will be easier if you don't convert to text, but leave the values as date/time serial numbers. I would probably do something =J12-I12, then format as "dd hh:mm" which will display as 3 02:30 (meaning 3 days, 2 hours, 30 minutes). If you go through the instructions for building custom number/date/time formats, there are other ways to display the result as well https://support.office.com/en-us/art...2-09fab54be7f4

Then, to get percentage time elapsed, you simply need to divide the elapsed time [=NOW()-start date/time)] by the total time (assume this is the J12-I12 calculation) to give =(NOW()-I12)/(J12-I12) or =(NOW()-I12)/(cell containing above calculation) formatted as per cent.

[Sparkytech]

Thanks for the help so far. I have it working better now, but I still need to simplify my formulas. The “Percent complete” seems to be working, although when I expand the table, sometimes that calculation works, sometimes it doesn’t. Sheet has been uploaded, any ideas here?

[MrShorty]

How much of this is now the "percent complete" calculation, and how much is the complex IF() logic that decides when to perform the "percent complete" calculation? It also might help to explain which of the sample calculations included in your latest file are incorrect.

The only calculation that looks wrong to me is the one in row 15 where it is calculating 0%. Using the Evaluate Formula tool (https://support.office.com/en-us/art...6-a70aa409b8a7 ), I see that this result is mainly because the IF() logic sees that B15 is not equal to "started", so it chooses not to even perform the "percent complete" calculation. If I set B15 to "started", then the result seems correct.

In short, it seems to me that the percent complete calculation is correct. I am not sure I understand the full logic you are trying to implement that chooses when to perform the percent complete calculation, but it looks to me like the error is in that decision logic. Is my analysis correct? Can you explain exactly how you want to decide when to perform the percent complete calculation? Am I missing something else?

[Sparkytech]

The sheet works as follows:

If “B” = “Not Started” or “Cancelled”, set % Complete to 0%.
If “B” = “Completed”, set % Complete to 100%.
If “B” = “Started”, calculate % Complete based on today’s date/time versus end date/time.
The other “IF” statements are error control and automation. I.e.: If the dates are reversed or missing, etc., display text in the cell. I am going to simplify the formulas and combine the “AND” and “OR” statements after I am done testing and troubleshooting.

When I use the first row, it always calculates. When I add a new row to the table, the % Complete doesn’t always work.

[JeteMc]

I believe the reason that you encountered the issue "When I use the first row, it always calculates. When I add a new row to the table, the % Complete doesn’t always work" is due to some of the structured references not being targeted to the specific row. See if this helps:
1) Select cell k12,
2) Paste the following formula into the formula bar:

Please Login or Register  to view this content.

3) Drag the fill handle down to cell K15.
If this doesn't resolve the issue please upload a sample showing the errant calculation, and of course tell us the correct/expected value.
Let us know if you have any questions.