The attached file is a downloaded report. It shows students who take and finish courses. the start and finish time don't show whether is AM or PM. I need to calculate the difference between the finish date and start date, as well as the difference between the finish time and start time. The score 100 means the student successfully finished the course and it shows the finished time. However, I would be also interested in knowing how to calculate the time spent on those unfinished courses. Would it be possible to do?
What have I done so far?
- I used right and left formulas to cut off the weird numbers at the end of the date/time cell.
- I did also used right and left formulas to extract date, and then I subtracted the finish date from start date, of course only for the students who finished a course.
- for some reason I couldn't use the right and left formula to calculate the difference between finish time and start time, it showed weird results, so I did use the MOD formula. =MOD(F7-D7,1).
Please note, I had to convert text to values in some of the steps above. All the above worked to some point, until I filtered the start date/time by months, and use the ALT plus semi colon ; to extract only filtered values, copied and pasted the filtered rows and values looked totally off.
My goal is to be able to show in one report the monthly time spent for each student, and compare the progress by month. I'd would create pivot tables and hopefully a dashboard.
I would be very thankful if somebody can look into this and be able to suggest a much effective approach to my dilemma.
Sincerely,
Dina
Hello Dina,
Welcome to MOTH.
You can find more useful information here on how to work with date/time in Excel.
If you are familiar to Power Query I suggest you use that tool to clean up your data, below is suggested steps to clean the data using formulas and helper columns, see attached file.
NOTE: As I work in Swedish regional settings I use semi colon (;) as separator in the below exampel formulas, you need to change those to comma (,) if you want to copy and paste the formulas. Also in my TEXT formula I use t to get the hours, you need to change that to h to get correct values.
- As you have done yourself, you need to clean up the start and finish date/time values. Example formula below is to clean up the start date/time in your sample file, column D. The result will give you a correct Excel date/time value as a number, which helps when to calculate the difference in days and hours.
=IFERROR(DATEVALUE(LEFT($D2;FIND(".";$D2)-1))+TIMEVALUE(LEFT($D2;FIND(".";$D2)-1));"")
- Next is to calculate the difference between the dates and times. In this example I add some helping text to the days and time values. In this example the date/time is converted back to text.
=IFERROR(
IF(INT(H2-G2)=0;
TEXT(H2-G2;"t")&" hour(s) "&TEXT(H2-G2;"m")&" minute(s)";
INT(H2-G2)&" day(s) "&TEXT(H2-G2;"t")&" hour(s) "&TEXT(H2-G2;"m")&" minute(s)");
"")Another example with no helping text.
=IFERROR(
IF(INT(H2-G2)=0;
TEXT(H2-G2;"tt:mm");
INT(H2-G2)&" "&TEXT(H2-G2;"tt:mm"));
"")
