Calculate time remaining based on end date & time and current date

Hi, this is my very first post so apologies if I do not enter this correctly but this is the issue I have:

I have a 2010 excel spreadsheet where I have the following columns:

Start Date - entered manually
End Date - which has a formula based on the start date & time plus 48 hours =C4+G$1 to automatically popolute this field. I need to copy the formula down the spreadsheet but would like the end date column to be blank if the start date field is empty. Additionally, the formula needs to exclude weekends and holidays.

Time Remaining - needs to automatically show the time remaining based on the end date & time and current date & time excluding weekends and holidays.

C4 = End Date above
G$1 = set hours i.e. 24:00, 36:00 or 48:00 ect. This will be a set number of hours for every row to determine the end date and time


Any help would be greatly appreciated as I am pulling my hair out now :-( I have seen formulas containing MOD and MEDIAN but I don't understand what these are and how they work.

Hi,
For end date to be blank, when start date is blank, you can use =if(Start_Date="","",Your_Formula)

For time remaining, you can explore NetWorkDays formula

Hi,
The blank cells were great now, thanks, but how do I exclude weekends and holidays from the formula.

I have also tried using the NetWorkDays formula but I must be doing something wrong as I just get a lot of ######### as the answer.

Thanks,
Kaz

Networkdays works like this =Networkdays(Start_Date,End_Date,Holidays_Range)

You can store holidays date in a range and use it in the formula. Networkdays automatically excludes Saturday and Sunday. If you still dont get it, share a dummy workbook.

Thanks for your help. I have attached the dummy workbook for you to look at.

Thanks,
Kaz

Wow, that's great - thank you. Now I just need to resolve the time remaining column to incorporate working hours.

Thank you very much
Kaz

That's really great. The only thing I noticed is that when you reach the end date & time the formula still shows a time remaining figure, from where I don't know. It would be great that if you have passed the end date and time the cell just shows zero. Is this at all possible - sorry to be a nuisance.

Thanks,
Kaz

Try this formula, if your problem is not resolved yet.

Formula:

Please Login or Register  to view this content.

Hi
Thank you for coming back. When you have passed the date and time the cell sits at zero, which is what I need but unfortunately it does not calculate the remaining live hours correctly. See attached spreadsheet - row 8.

Any help would be appreciated.

Thanks,
Kaz

Hi,

The values given in column C are correct answers? For row 8, it is showing zero. The formula i have given also showing zero.

Hi,
The rows above row 8 have a different formula in column C that calculates the remaining hours but when it gets to 00:00 it starts to count upwards again once the end date and time have passed. When this point has been reached I just want it to sit as 00:00 hours remaining.

With the formula you gave that is shown in row 8 it does not calculate the remaining time correctly based on the difference between NOW() and column B End Date and Time. What your formula does do is it sits at 00:00 time remaining if there is no time remaining between NOW() and End Date and Time, which is correct but if there is remaining time I need this to calculate the actual remaining time.

Thanks in advance for your kind help and support.

Kindest regards
Kaz

Then can you provide the answers to the rows in your sheet.. by freezing now() to a particular time.. so that i can check where it is going wrong..

Sorry, how do I freeze now()?

Thanks,
Kaz

What i mean is, say as on today 13:00, what should be time remaining.. Since now keeps changing, you can provide a stable answer to me rite..

Hi,

I have replaced the now()with cell H10, which has a set date and time in it. As you can see the formula has not calculated the correct time remaining. Time remaining should be 6:40 and not 9:30.

Thanks,
Kaz

Check this one..

Formula:

Please Login or Register  to view this content.

Wow, this is fantastic. I didn't think it was possible what I wanted. You are the best and this is the best forum I have been on. What a great start for a first time user. Thank you so very much for your hard work, I really appreciate it.

Kindest regards,
Karen

The same way as given in post 2.. if(Start_Date="","",Your_Formula)

Sorry, when I trying add the above ..if(start_date="","",my_formula) it doesn't like it. Would you mind writing it exactly as it should be I must be doing something wrong

Thank you
Kaz

Formula:

Please Login or Register  to view this content.

Hi just now checked, if today's date is in holiday's list there is an issue. Fixed the formula.. check this..

Formula:

Please Login or Register  to view this content.

Hi,
So sorry to be a pest, I now have the blanked out cells until a date/time is entered in to column A but the remaining time calculation has gone wrong as you can see from the highlighted rows in yellow. Rows 17 & 18 with the new formula are showing the incorrect time remaining.

Thanks,
Karen

Sorry, added the wrong spreadsheet. This is the correct one.

Thanks

Check the formula in D column. Used named formulas to avoid confusion.

Hi,

Thanks for the formula - it works a treat. I noticed that you have used what looks like to me some hidden named fields such as Is_Today_Holiday, which is why I am struggling to use the formula on a new workbook. How can I create the named fields to match my new workbook.

Kindest regards,
Kaz

Hi,
Just to confirm I have managed to copy over the formulas using the Name Manager field so now everything seems to be okay.
Many thanks for all your support and patience.
Kindest regards,
Kaz

Hi daddylonglegs,

Should H$7 in the formula above be F$7 (start time) otherwise what does H$7 refer to?

Many thanks,
Kaz

Thank you, that works really well and very easy to use. Thank you for taking time out to help me resolve my query.

Kindest regards,
Kaz

Hi, this is my very first post so apologies if I do not enter this correctly but this is the issue I have:

I want to calculate the remaining time between the now time and end time. the formula i use is Time Now() minus Time End
I get an answer of 19:26:28 this is not correct considering the start time is 12:00 and the duration is 05:12.

Please advise how I can fix this.

Thanks
Rand_32