Excel calculation for adding time and calculating remaining time

Hi Guys,

Need some help on formula in Excel. I have attached the file.

Book1 Test.xlsx

Basically I have in cell A1 Open Time, Cell D1 Incident Time based on another Cell. I am trying to have a calculation for column E1,F1,G1,H1 and I1 as below:

E1 - Column B1(open time) plus column D1(1 day 16 hours). Tried B1+D1 but it does not work.
F1 - Show duration of hours remaining from current time minus column E1. Tried Now() minus Duration in E1 but does not work. It shows #VALUE!
G1 - If ticket will expire by today then should show as Y else show as N
H1 - If ticket due to expire by tomorrow only then show as Y else show N
I1 - If ticket will expire more than 2 days time show as Y else N

Hope someone can help me on this?

Thanks Guys.

Regards,
Mark.

Hello in cell B2 you have format as
m-d-yyyy hh:mm:ss
I think you need this format
dd-mm-yy hh:mm:ss
Is it so?
Please clear...
Regards..
Sourabh

Originally Posted by sourabhg98

Hello in cell B2 you have format as
m-d-yyyy hh:mm:ss
I think you need this format
dd-mm-yy hh:mm:ss
Is it so?
Please clear...
Regards..
Sourabh

Hi Sourabh,

That is how the data was generated. I think it should be no problem if it's dd-mm-yy as long as it work but I still can't get it to work.

Regards,
Mark.

Hmm... Ohk thanks ...I will just be back with the possible solution

Originally Posted by sourabhg98

Hmm... Ohk thanks ...I will just be back with the possible solution

Thanks Sourabh.
Appreciate it.

Regards,
Mark.

Hey check out this one in E2
=(DATE(YEAR(B2),MONTH(B2),DAY(DAY(B2)+LEFT(D2,2))))+(TIME(HOUR(B2)+MID(D2,4,2),MINUTE(B2)+MID(D2,7,2),SECOND(B2)+MID(D2,10,2)))
Till then I am working upon other formulas

Originally Posted by sourabhg98

Hey check out this one in E2
=(DATE(YEAR(B2),MONTH(B2),DAY(DAY(B2)+LEFT(D2,2))))+(TIME(HOUR(B2)+MID(D2,4,2),MINUTE(B2)+MID(D2,7,2),SECOND(B2)+MID(D2,10,2)))
Till then I am working upon other formulas

Hi Sourabh,

Yes that works perfectly for E2 but noticed that only works if cell B2 is in format dd-mm-yy hh:mm:ss. What if its in m/d/yyyy hh:mm:ss? It wont work when i tried it.

Regards,
Mark.

Originally Posted by mark888

Hi Sourabh,

Yes that works perfectly for E2 but noticed that only works if cell B2 is in format dd-mm-yy hh:mm:ss. What if its in m/d/yyyy hh:mm:ss? It wont work when i tried it.

Regards,
Mark.

Hi Again Sourabh,

The formula you provided when I inswerted column B2 with 08-01-2015 22:00:00 and D2 with 00 08:00:00, the result shows is 08/01/2015 06:00:00 when it should be 09/01/2015 06:00:00 instead. Any ideas on how to fix this?

Regards,
Mark.

Hello Mark
When I tried its working for that format also.....

The formula in cell B2 would not effect the formula but it would effect if format of D2 is changed

Originally Posted by sourabhg98

The formula in cell B2 would not effect the formula but it would effect if format of D2 is changed

Hi Sourabh,

I tried on my Excel sheet but it does not work.

Book1 Test.xlsx

Regards,
Mark.

Hello
I have made all the formulas and they all are working for the format m-d-yyyy hh:mm:ss
Please once again check your formats and tell me if they still don't work....

Regards
Sourabh

Originally Posted by mark888

Hi Again Sourabh,

The formula you provided when I inswerted column B2 with 08-01-2015 22:00:00 and D2 with 00 08:00:00, the result shows is 08/01/2015 06:00:00 when it should be 09/01/2015 06:00:00 instead. Any ideas on how to fix this?

Regards,
Mark.

Sorry for that mistake ....Let me check it

Originally Posted by sourabhg98

Hello
I have made all the formulas and they all are working for the format m-d-yyyy hh:mm:ss
Please once again check your formats and tell me if they still don't work....

Regards
Sourabh

Hi Sourabh,

Yes its working wonderfully now although I made a slight mistake. In column F1 is it possible to show the hours in the format of hh:mm:ss instead of the total hours?

Regards,
Mark.

Originally Posted by mark888

Hi Sourabh,

Yes its working wonderfully now although I made a slight mistake. In column F1 is it possible to show the hours in the format of hh:mm:ss instead of the total hours?

Regards,
Mark.

Hi Sourabh,

Just tested your formula and when I tried inserting B2 with 08-01-2015 22:00:00 and D2 with 00 08:00:00, it is still resulting in 01-06-2015 00:51:00. It should be 02-06-2015 00:51:00 instead since once added 8 hours from 2200 it should move on to the next date.

Regards,
Mark.

Originally Posted by mark888

Hi Sourabh,

Just tested your formula and when I tried inserting B2 with 08-01-2015 22:00:00 and D2 with 00 08:00:00, it is still resulting in 01-06-2015 00:51:00. It should be 02-06-2015 00:51:00 instead since once added 8 hours from 2200 it should move on to the next date.

Regards,
Mark.

Hi Sourabh,

Still does not work for columns G,H and I as when i tried inserting column F with 00 03:00:00 which is 3 hours, column G shud be Y because 3 hours from now will still be today's date. Any ideas on how to fix this?

Regards,
Mark.

Yes I was just checking that
Now see this one
Sorry for that
Also now in F1 it will give hh:mm:ss and will show expired if ticket is expired...

Mark is it working with this one??

Originally Posted by sourabhg98

Did you check it??
If it worked you can thank me by clicking on add reputation below
Regards
Sourabh Gupta

Hi Sourabh,

Sorry my internet got disconnected. I have checked and this time it works beautifully. Although if you dont mind me asking is it possible instead of the total hours is 4000 plus can it be in days if it is more than 24 hours? If it can't be done it's ok because this already works very well!!.

Regards,
Mark.

Originally Posted by mark888

Hi Sourabh,

Sorry my internet got disconnected. I have checked and this time it works beautifully. Although if you dont mind me asking is it possible instead of the total hours is 4000 plus can it be in days if it is more than 24 hours? If it can't be done it's ok because this already works very well!!.

Regards,
Mark.

Hi Sourabh,

Sorry found another error. When column F shows as Expired, column I still shows as Y. Is it possible if F shows Expired then column I will be blank?

Regards,
Mark.

Ok here we go....
If you are satisfied then just click on ADD REPUTATION below to say thanks.....

Originally Posted by sourabhg98

Ok here we go....
If you are satisfied then just click on ADD REPUTATION below to say thanks.....

Hi Sourabh,

Yes I really appreciate your help but there is still one problem. Whenever the time reaches to 00 00:00:00 the cell value all changes to error #Value! and does not show as Expired.

Regards,
Mark.

Ohk...see this--

Sorry for the mistakes....hahaha....Actually I am not an excel expert but still I try to solve problems....

Originally Posted by sourabhg98

Ohk...see this--

Hi Sourabh,

Yes this is just perfect!. Thank you very much for your time and expertise.
Regards,
Mark.

Originally Posted by sourabhg98

Sorry for the mistakes....hahaha....Actually I am not an excel expert but still I try to solve problems....

You look like an expert to me.

You are most welcome
Glad to know it worked...
Cheers!!!
And thanks for the reputation...