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
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
Last edited by a moderator: