Forum Discussion

GustavTC's avatar
GustavTC
Copper Contributor
Jan 20, 2020

Formel for Driving journal

Hi! 

 

i would like som help! At the firm im working i get lot of questions about Excel formulas! One specific question are if we can use a formula to  mark   out trips that our company cars make during work ours and on work days.

 

Every month we  get a sheet from Volvo On Call and in there it says the date och time that the car has started and  has turned off. but its hard to distinguish whats a private trip and whats a work trip.

 

So my ide is to make a sheet och a formula that marks out the work trips and the calculates the sum of the marked trips in km. 

 

Can it be done? 

thx for the help! Best regards Gustav Nilsson Sweden 

 

developer
excel
Excel for web
Excel on Mac
Excel on mobile
office 365

7 Replies

  • Michiao's avatar
    Michiao
    Copper Contributor
    Jan 20, 2020

    Hello GustavTC,

     

    How do you define if a record is private trip or work trip? Does it related to date and time?  

     

    Thanks,

    Michelle

    • GustavTC's avatar
      GustavTC
      Copper Contributor
      Jan 20, 2020

      Yes the timeframe is 07:00 to 16:00 and it should be just Monday -> Friday.

      Can excel determen the weekday by the date? 

       

      This is how one line can look 

      Uncategorized2019-12-03 13:1358383Grustagsvägen 2-4, Oskarshamn2019-12-03 13:1958386Ingenjörsvägen 30, Oskarshamn00:06 h3,60,3  

      Michiao 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 20, 2020

        GustavTC 

        The simple logic is like

        =IF( (WEEKDAY(A1,2)<6)*(MOD(A1,1)>=7/24)*(MOD(A2,1)<=16/24),"work", "private")

        if in A1 start datetime and in A2 end datetime.

         

        More complex is how to define if, for example, trip starts on Monday at 15:30 and ends at 16:30. But that's business logic, you shall define how to handle such situations.

Resources