How can i calculate as an hour between two dates?

Hi

I'm struggling a bit to understand how this works (i.e. which bits above are the errors and which bits are your desired result), but my thoughts are:-

Please Login or Register  to view this content.

This looks suspicious. This type of format command is normally used with dates and times. However, you are counting hours. Excel uses a decimal date format where the integers are the days and the decimal part is the time.

e.g. 40939.36875 represents day 40939 (31/1/2012) and the time is 0.36875 (8:51am).

If you show this value using the Format command with "h:n" format you will only see the decimal part (8:51am).

You could try dropping the format command. I would propose :

Please Login or Register  to view this content.

Another place to look is here:

Please Login or Register  to view this content.

If I understand correctly, Textbox10 contains the number of stop days. You want the total number of hours. However, you are subtracting the stop days. You could try:

Please Login or Register  to view this content.

Lastly, for TextBox10, you have the code

Please Login or Register  to view this content.

Consider this. Format(72,"d") = 12. Again, Excel thinks you want to know what day of the month, not how many days. So, if you go past 31 stop days, your code will break down (as it will role over to February). In general, I think none of your results should have Format commands in them.

If you're still struggling, please upload the file so we can see exactly what you are trying to do.

Hope some of this helps.

Cheers, Rob.

Hi Rob,

Firstly thanks for your answer. Let me explain what i want to do.
We have a maintenance file and we want to save records in small database. For some analysis, we classified stop times between two dates like stop days, stop hours etc. The formula as below;

Start date: 23.1.12 23:30
End date: 26.1.12 23:30
The results must be;
Stop hours (Dur.Saat)TextBox11: 0 (the formula is --> Total hours - (Total hours/24) the remaining time as a hour)
Stop Days (Dur.Gün)TextBox10: 3 (the formula is --> End date-Start date as a day)
Total Hours (Toplam Saat)TextBoxx12: 72 (the formula is --> End date-Start date as a hour)

If my method will break down (as it will role over to February) as you said, which method shall i use? What is your suggestion?
Sorry i can not upload sample file because of manage attachment does not working!

Cheers, Blckwolf

Originally Posted by rscsmith

Hi

I'm struggling a bit to understand how this works (i.e. which bits above are the errors and which bits are your desired result), but my thoughts are:-

Please Login or Register  to view this content.

This looks suspicious. This type of format command is normally used with dates and times. However, you are counting hours. Excel uses a decimal date format where the integers are the days and the decimal part is the time.

e.g. 40939.36875 represents day 40939 (31/1/2012) and the time is 0.36875 (8:51am).

If you show this value using the Format command with "h:n" format you will only see the decimal part (8:51am).

You could try dropping the format command. I would propose :

Please Login or Register  to view this content.

Another place to look is here:

Please Login or Register  to view this content.

If I understand correctly, Textbox10 contains the number of stop days. You want the total number of hours. However, you are subtracting the stop days. You could try:

Please Login or Register  to view this content.

Lastly, for TextBox10, you have the code

Please Login or Register  to view this content.

Consider this. Format(72,"d") = 12. Again, Excel thinks you want to know what day of the month, not how many days. So, if you go past 31 stop days, your code will break down (as it will role over to February). In general, I think none of your results should have Format commands in them.

If you're still struggling, please upload the file so we can see exactly what you are trying to do.

Hope some of this helps.

Cheers, Rob.

Could you help me somebody?

Could you help me somebody? I need solve the problem immediately.