creating countdown timer in excel no vba

[lezawang]

Hi

I am trying to do similar countdown timer (please see the link below) but my problem, when I subtract the target hours from the start hours, the result is negative. The author did not say anything about that. How can I fix that. I also appreciate if you have better idea of doing that. Thank you so much.

https://stevechasedocs.wordpress.com/2014/11/19/create-a-countdown-timer-with-dates-in-excel/

 

[Michael M]

It's not a minus sign....it's simply a text seperator.
Did you give The example a try ??

 

[lezawang]

Thank you. You are really fast I edited the post. Yes I realized after posting that is not - sorry for that. I tried that example and the problem I am having now is that subtracting minute of the target date from the now date is negative. How can I fix that. Thank you so much.

My start date : [TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl64, width: 111, align: right"]25/10/2019 21:35

[/TD]
[/TR]
</tbody>[/TABLE]

My end date:
[TABLE="width: 232"]
<tbody>[TR]
[TD="class: xl65, width: 232, align: right"]25/12/2019 21:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Change the formula in H1 to

=ABS(INT(E1-B1))

 

[lezawang]

Perfect, thank you so much that resolved Minutes and Seconds issue. I have problem with Hours. The author used this formula

[FONT=&quot]Hour =IF(E2>=B2,E2-B2,24-B2+E2)

E2 is the event hour
B2 is now hour

Why added 24? is that because of Cross Midnight? I read cross mid-night can be done like this

= if(end>=start, end-start,end+1-start)

Anyway I did what the author did,

My now hour = 22
My Event hour = 21

The result to subtract them is 25!! clearly something wrong.

Thanks a lot


[/FONT]
[TABLE="width: 880"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Today's date and time[/TD]
[TD="align: right"]25/10/2019 22:00[/TD]
[TD][/TD]
[TD]Event date[/TD]
[TD="align: right"]25/12/2019 21:00[/TD]
[TD][/TD]
[TD]Days[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]Hour[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD]Event hour[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD]Hours[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]Minute[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Event minute[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Minutes[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Second[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]Event second[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Seconds[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Yes 24 is across midnight.....and using your

My now hour = 22
My Event hour = 21

and the authors method returns 23 for me ??

 

[lezawang]

Thank you very much. Do I need to format these cells which have the hours? I am not getting the right answer no matter what I do. Thank you.

 

[Michael M]

Apart from my initial modification, I did not change any other feature of the timer, including the formats.
Are you using the downloaded version OR creating your own ?
I'd suggest playing with the download version, if you are not doing so !

 

[lezawang]

Thank you so much. I did not know there is a downloading version. I downloaded and I had an error in the formula. Now it is working. I have one more question please. Why subtract from 24? I know it is because of midnight but I have seen other video Add 1 to the end time of the end time is less than the start time. Here he did totally different, he subtracted 24 from the start time then added to the end time. That part confuses me. Thank you so much.