+ Reply to Thread
Results 1 to 8 of 8

How to create a real time report with on/off time and duration?

  1. #1
    Registered User
    Join Date
    11-19-2013
    Location
    HK
    MS-Off Ver
    Excel 2007, 2010, 2013
    Posts
    8

    How to create a real time report with on/off time and duration?

    Hi,

    I have long list of data on usage (time on and off in a day) of 29 vehicles like below.

    data.jpg

    I would like to convert it into something like this: idea.jpg (a demo)

    which shows whether it is occupied for each vehicle at any given time (in minute).

    I am new to the forum and Excel so any advice will be very much appreciated.

    Thanks a lot!

  2. #2
    Registered User
    Join Date
    09-21-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: How to create a real time report with on/off time and duration?

    Hello Why123

    Can u please upload a sample file (Excel file instead of Jpg file) and your expected results so that some one
    can help you .

    regds
    kmahesh

  3. #3
    Registered User
    Join Date
    11-19-2013
    Location
    HK
    MS-Off Ver
    Excel 2007, 2010, 2013
    Posts
    8

    Re: How to create a real time report with on/off time and duration?

    Hi Kmahesh,

    I have attached the excel file. Excel.xlsx

    The idea is just a rough thought. It will also do if the usage is presented in time line format instead of columns. The main idea is to compare each vehicle side by side against the real time.

    Thanks for any help in advance!

  4. #4
    Registered User
    Join Date
    11-19-2013
    Location
    Taiwan
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: How to create a real time report with on/off time and duration?

    hello, I wish I have extensive knowledge about these but I don't. I'm a newbie in the forum as well. I am trying to learn more about excell too.

  5. #5
    Forum Contributor Bhuvi's Avatar
    Join Date
    04-19-2013
    Location
    Delhi, India
    MS-Off Ver
    MS Excel 2003,07,10
    Posts
    153

    Re: How to create a real time report with on/off time and duration?

    Hi Why123,

    Welcome to the forum In the attached sheet is one of the probable solutions in sheet2.
    Green is the time on color and orange is the time off color. It's similar to what you expected. I hope it's useful for you.


    Note: You have to do some work in sheet"Data",
    The Time off and time on should not be the same for a vehicle eg. time off Tr1 2:32:00 AM and next time on should be 2:33:00 AM. I have change it till TR 26, rest please correct it and paste if for the rest.

    col E and F have formulas so copy them down when you add new data.

    In col I you have to merge all data from col B and col C, remove duplicates and sort ascending, then paste it in sheet2 col A.


    <if this helped pls click on the star * below to add some reputation>
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-19-2013
    Location
    HK
    MS-Off Ver
    Excel 2007, 2010, 2013
    Posts
    8

    Re: How to create a real time report with on/off time and duration?

    Hi Bhuvi,

    Thanks for your prompt reply.

    I have opened the Excel file (in 2007 version) but found that sheet2 only contains row and column labels. I am not sure if I missed anything but I couldn't see the "Green is the time on color and orange is the time off color".

    Sorry I am still a newbie on Excel. May I have more explanation on how to use your suggested solution?

    Thanks a lot!

  7. #7
    Forum Contributor Bhuvi's Avatar
    Join Date
    04-19-2013
    Location
    Delhi, India
    MS-Off Ver
    MS Excel 2003,07,10
    Posts
    153

    Re: How to create a real time report with on/off time and duration?

    It's because of the version of excel, I used 2010.
    Formulas in conditional formatting in Excel 2007 cannot refer to a different sheet.

    So now I have transferred the coloring from sheet2 to sheet"data". Now you don't have to copy col I to sheet 2, keep it in the same sheet. You can use it in excel 2007 version.
    Attached Files Attached Files
    If this helped and you wish to say thanks, then Please click on the Star* icon below this post.

  8. #8
    Registered User
    Join Date
    11-19-2013
    Location
    HK
    MS-Off Ver
    Excel 2007, 2010, 2013
    Posts
    8

    Re: How to create a real time report with on/off time and duration?

    Dear Bhuvi,

    Thanks a lot for your second file. It took me some time to correct my data but it is all working great now.

    Thank you so much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Split Time Duration to first complete the running hour and then go to End time
    By joogibabu in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-06-2013, 10:56 PM
  2. Formula for Calculating Paid Time Off for fiscal anniversary date real time
    By 168rockwood in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-07-2012, 04:15 PM
  3. Calculate finish time given start time, working hours and job duration
    By swanseaexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2012, 03:00 PM
  4. Replies: 4
    Last Post: 01-11-2012, 07:59 PM
  5. Documenting Static Real Time [Absolute Time] in a Cell
    By SDruley in forum Excel General
    Replies: 9
    Last Post: 06-23-2011, 10:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1