Hi
Attached is the excel file and wish to know:
a) Total time of the journey (e,g, journey is of more than 12 hours so the total time from 09:20 to 01:20 is 16 hours). Please help with the formula to calculate so.
b) Formula to make a negative value (already entered) into positive in the next cell.
c) If train takes more than 16 hours, color red; 12-16 hours, color yellow; <12 hours, color green.
Thanks
Sushil
Sushil,
Attached is a modified version of your example workbook. In cell J5 is the formula that calculates the total journey time, ensuring that journeys are longer than 12 hours as requested:
=(IF(HOUR(I5)<HOUR(H5),HOUR(I5)+24,HOUR(I5)+12)-HOUR(H5))/24+(MINUTE(I5)-MINUTE(H5))/24/60
For question b) there was never a need to make negative numbers positive, but if you do need to do that, this formula will make it happen:
=SQRT(A1^2)
See attached for how the conditional formatting works. However, with the formula ensuring no journey is less than 12 hours, it doesn't seem possible to have the green condition met...
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
i think he wants it to be able to realize above 12 hours in the event that it happens, but to also know when its only been 2 hours etc.
with that said, id suggest swapping to army time if possible (never checked)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks