Solved: Calculate Time Left

[lynnnow]

Hi Guys,

I've got a a worksheet that I use to calculate time elapsed and time remaining.

The time elapsed is a simple formula, but getting the time left is flipping me.

The server from where the base time is taken is set on EST, and my PC is set to IST (Indian Time). I don't want to change this set up.

The base time is stored as "m/d/yyyy hh:mm:ss". To get the time elapsed, i use:

(NOW()-P2)+TIMEVALUE("14:34")

Cell P2 has the base time. I've used 14:34 as the addition since, that matches the time elapsed as shown on the site.

I'm stuck with the time remaining. Please help.

[lynnnow]

I'm assuming that the date difference is what is causing this thing to bug out. I changed the system time to EST and PST, and it works. Just if the system is set to IST, it causes problems.

[Aussiebear]

Why would that be? Excel doesn't know if it is being used in India or Greenland, since it converts a date to a number.

[lynnnow]

Hi Aussiebear,

That's correct, but how do I take the time zones into consideration for the calculation.

I've attached the sheet for your reference. Sheets 1 and 2 are my concern. You can ignore sheets 4 and 5.

Sheet 1 is where the raw data is copied to. Sheet 2 converts this raw data to formatted data. Then I just copy some of the columns for my report. The time elapsed on sheet 2 is column F and time remaining is column H.

On sheet 1, column N is just test attempts, please ignore.

Thank for your help.

[Bob Phillips]

The dates are not working in that workbook!

[lynnnow]

If you're looking at Sheet 1, then yes, the dates don't work since they are in a rather odd format, see columns O and P to see how I've formatted them. It's crude but it works.

But please explain which sheet the dates don't work on.

[Aussiebear]

Sheet 2 has a number of Value results which are derived from another sheet. I personally do not have the time to track down and correct your worksheets. Please present a "working" workbook and then we'll look further into this matter

[lynnnow]

Aussibear, this is the working worksheet. Each row on Sheet 2 represents a value from Sheet 1, just formatted a bit differently. The reason it shows the #VALUE error is because there is no data in that corresponding row on Sheet 1.

[Bob Phillips]

No, even the first row shows #VALUE at the relevant points, those that pick up the date. we look ti help, but as Aussiebear says, don't lay it all on us.

[lynnnow]

Hi xld, Aussibear and friends,

Thank you for being patient and I really appreciate your help.

The file I posted had only one row of data. Here I'm posting with a few more rows of data.

Column G on sheet 2 is irrelevant, I've deleted it. My problem is with column H on sheet 2. The rest of the formulae are fine.

I've used this same workbook without problems and without any changes to formula for the past 6.5 years and there hasn't been any problem all this while. The only updates I had to make were the daylight saving updates on sheet 1 in column M (there's a macro in the workbook for it, which I do not use currently).

I've changed companies, but the project has remained the same, and here I've got to use a PC with the system time being in IST. I have adjusted the formula to get the elapsed time (column M, Sheet 1), but it is the time remaining that's bugging me out.

For e.g., a file is received at Jul 26, 2011 16:27:00, and if the file has a TAT (turn around time) of 24 hours, it should be returned by Jul 27, 2011 16:27:00. If the file has a 4 hour TAT, the file should be returned by Jul 26, 2011 18:27:00. I can get the time elapsed, but the formula for the time remaining, considering the time difference between the time received, plus the time adjustment, minus the time elapsed is what is dodging me.

Please be patient, I've been struggling with this formula now for the past 4 months.

Lincoln

[lynnnow]

Edit to previous post: It should be read "If the file has a 4 hour TAT, the file should be returned by Jul 26, 2011 20:27:00"

[Aussiebear]

Your sample workbook is still not up to speed. Please indicate column headings so I can understand what is being calculated.

From reading your post#10 Sheet1 Column M formula currently reads =(NOW()-P1)+TIMEVALUE("14:34"), yet cell P1 has an error #value, so how can this work itself out?

Further more, Sheet 2 Cell H1 formula reads =IF(LEFT(Sheet1!G1,1)="2",(Sheet1!P1+1)-(Sheet1!P1+Sheet1!M1),(Sheet1!P1+TIMEVALUE("4:00"))-(Sheet1!P1+Sheet1!M1)). How can this be calculated if it relies on two cells in sheet 1 which have #Value errors?

As i indicated in post #7 please present a working workbook.

[lynnnow]

Ok Aussiebear, I understand now why you can't seem to make the sheet look operational. That's probably due to the difference between your time zone and mine. If you edit the "14:34" to approx. 5 hours more than that, the value should be calculated. I've added 14:34 to compensate for the time difference. The server from where I copy the raw data is set to EST, my PC to IST. Hence the 14:34, to represent the exact number of hours elapsed considering the time difference between the server and my PC. As for column headings, I'm sorry to have confused that bit. I haven't put in column headings since I don't need it. Another part of my code starts reading at row 1, but here is what it should be. It is a screenshot of Sheet2 with the column headings.

[Aussiebear]

Ok Aussiebear, I understand now why you can't seem to make the sheet look operational. That's probably due to the difference between your time zone and mine.

I can't make head nor tail of your workbook because its not a working copy. It has nothing to do with time difference. Fix the errors before uploading or this will finish here.


I haven't put in column headings since I don't need it.

You may not need it but we do. Since we are not as familiar with the workbook as you then you need to make it self explanatory for others


Another part of my code starts reading at row 1, but here is what it should be. It is a screenshot of Sheet2 with the column headings.

Please re read post#11 and try to follow the logic through. Your formula in Sheet2!H2 requires data from Sheet1. But the cells in Sheet1 are unable to calculate because they themselves are subject to #Value errors. And this is yet another reason why its not a working file.

[lynnnow]

I'm closing this thread. It has caused enough anguish for me and for you guys. I'll try a work around, and I know it is going to be an arduous task without you guys. Thanks for your efforts.