• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating total hours between i.e. 7.30am to 4.30 pm

M

melvin

Member
Gday all,

Was wondering if I could get assistance in calculating total hours between the range mentioned in the file.

Appreciate it.

Melv.
 
Melvin

The issue here is that the cells with Times are text and not actually times
Times are simply number between 0 (Midnight) and 1 (the following midnight)
eg: 0.5 is Noon, 0.25 is 6 am 0.75 is 6 pm

If you select say cell E5 and put a space between the 00am so it is 00 am and then remove the spaces before the 9, repeat on all the times
Then in G5 simply use =F5-E5

You will have to use a Custom number form of say hh:mm
 
Let me try the above mate. Will keep you posted.

As I get this data dump from the data warehouse, I would manually have to find a way to formatting as above to the entire data set. Let me try if I can use any trick to get it.

Cheers,
Melv.
 
If you are regularly importing the data you can use a simple formula:
G5: =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(F5,"P"," P"),"A"," A"))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(E5,"P"," P"),"A"," A"))
copy down

Then format as hh:mm
 
You must log in or register to reply here.
Back
Top