creating countdown timer in excel no vba

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,519
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
It's not a minus sign....it's simply a text seperator.
Did you give The example a try ??
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,519
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 :
25/10/2019 21:35


<tbody>
</tbody>

My end date:
25/12/2019 21:00

<tbody>
</tbody>
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
Change the formula in H1 to

Code:
=ABS(INT(E1-B1))
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,519
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]

Today's date and time25/10/2019 22:00Event date25/12/2019 21:00Days60
Hour22Event hour21Hours25
Minute0Event minute0Minutes0
Second9Event second0Seconds9

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Mar 27, 2016
Messages
1,519
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Mar 27, 2016
Messages
1,519
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.
 

Forum statistics

Threads
1,082,278
Messages
5,364,201
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top