Forum Discussion

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 d...

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
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
Uncategorized 2019-12-03 13:13 58383 Grustagsvägen 2-4, Oskarshamn 2019-12-03 13:19 58386 Ingenjörsvägen 30, Oskarshamn 00:06 h 3,6 0,3
Michiao
- 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.
  - GustavTC
    Copper Contributor
    Jan 20, 2020
    Thanks 😁
    
    Can you add to that formel that it calculates a total of att work trips? And marks Them out with a X r somthing?