'How to fill down time tracker if having only start & End MM:SS
Having Excel where I get required hourly down time with only having start & end date time as per the images required any formula or vba command for calculation.
Solution 1:[1]
Please, notice you are mixing different types of data in your output. Value 03:39 is a time value while 60,0 or 0,0 is decimal. You should consider working all of them in decimal if you are going to make any calculations.
Also, you could play with CF rules to see the values as you wish, but real value should be decimals.
Anyways, I managed to do it with this:
=IF(HOUR($A2)>HOUR(C$1);0;IF(HOUR($A2)=HOUR(C$1);TEXT("00:"&MINUTE($A2)&":"&SECOND($A2);"mm:ss");IF(HOUR($B2)>HOUR(C$1);TEXT(60;"0,0");IF(HOUR($B2)=HOUR(C$1);TEXT("00:"&MINUTE($B2)&":"&SECOND($B2);"mm:ss");TEXT(0;"0,0")))))
Drag to right and bottom.
Notice I'm forcing the format with the TEXT function, so the output is not numeric but a text so you cannot do any calculus with it. That's why you should consider using CF rules
Also, depending on your regional setup, masks like mm:ss or 0,0 that I use in the formulas may be different in your PC
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Foxfire And Burns And Burns |