Working time setting instead of Excel formulas in VBA?

[Nikko]

Hi to all,
How can I format the cells (with VBA) so that the 00:00:00 formatting remains in the cells, no matter how I enter the end or beginning working time?

With formula it would look like this “ [hh]:mm “.


Example:

--------------------------------------------------------------------------------

…………..A…………….…. B………………..C………..…... D

Work Beginning - Work End - WorkTime - NightTime(after 21:00)
..............12:00................... 24:00..................12..............................3
..............12:00................... 00:00..................12..............................3


(A column=A6-A36)

(B column=B6-B36)

C & D the same.

Formula in column C --> =IF(B6<A6;((B6+1)-A6)*24;(B6-A6)*24))

Formula in column D --> =IF(ISNUMBER(A6)*ISNUMBER(B6);IF(B6>TIMEVALUE("21:00");B6-MAX(TIMEVALUE ("21:00");A6);0)+IF(A6< TIMEVALUE("6:00");MIN(B6;TIMEVALUE("6:00")-A6);0);"")

-----------------------------------------------------------------------------------

If I end the working time with 00:00 then it does not calculate the NightTime.

If I start my working time at 24:00, it does not calculate the working time.

How do I get it so that the working hours, no matter how I enter them, add up the working hours and the NightTime??



Thx in advance

Nikko

 

[Nikko]

Any kind of help is welcome

 

[Nikko]

Found by my own a solution with Formula, so far i can leave with the formula

=(MAX(0;MIN(D6+(C6>D6);MAX(($AM$1>$AN$1);$AN$1))-MAX(C6;$AM$1))+MAX(;MIN(D6;MAX(($AM$1>$AN$1);$AN$1))-MAX(;$AM$1))*(C6>D6)+MAX(;MIN(D6+(C6>D6);MIN(($AM$1>$AN$1);$AN$1))-MAX(C6)+MIN(D6;MIN(($AM$1>$AN$1);$AN$1))*(C6>D6))

AM = 21:00
AN = 06:00

anyway...if someone have a solution with VBA, is welcome