Forum Discussion
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...
Hello GustavTC,
How do you define if a record is private trip or work trip? Does it related to date and time?
Thanks,
Michelle
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 |
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.
- 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?It's better to have sample file. For such model
to mark as work or private
=IF( (WEEKDAY(A1,2)<6)*(MOD(A1,1)>=7/24)*(MOD(B1,1)<=16/24),"work", "private")and total sum for all work trips
=SUMPRODUCT(($E$1:$E$30="work")*($B$1:$B$30-$A$1:$A$30))*24Result is in hours digital.
