Excel calculate the duration between two weekdays time

Question

Hi,

I have this table with me, each record represents one file. My objective is to calculate out the duration for the file to complete.

I stuck at some files are completed within the same day, while some files might complete only after 1 or 2 or 3 days.

Are there any formulas to calculate it out?

Thanks.

Answer 1

I think I found a way to achieve that. Solution

First I would apologize for my unclear question. The weekdays are text format, the weekend doesn't count, and the durations are confirmed within one week period.

I actually did the below steps and achieved to my objective.

1. Convert all the weekdays to numbers, refer to column J and K.
2. Column L is the variance between column J and K. L=K-J

3. Then the duration M column put this formula will do.

   =IF(L2=0,MOD(I2-G2,1)*24,IF(AND(L2=1,I2>=G2),MOD(I2-G2,1)*24+L2*24,IF(AND(L2=2,I2>=G2),MOD(I2-G2,1)*24+L2*24,IF(AND(L2=3,I2>=G2),MOD(I2-G2,1)*24+L2*24,MOD(I2-G2,1)*24+(L2-1)*24))))

Answer 2

You are better off with a single date and time column then use something like the below. Otherwise combine the date and time yourself first

=(TIMEVALUE(B2)-TIMEVALUE(C2)+(TIMEVALUE(B2)<TIMEVALUE(C2)))*24