Newbies mrmogway Posted March 26, 2011 Newbies Share Posted March 26, 2011 Hi, I am hoping someone can help with this seemingly simple formula. I need to know the exact midpoint of various given dates and times. Data is given as follows; start date 03/10/2011 @ 14:26 and end date 3/17/2011 @ 13:22 I can do this by crude formulas but I bet there's a simple way. No offense to FM power users but in Excel it's ridiculously simple. Thank you in advance for any help. Link to comment Share on other sites More sharing options...
comment Posted March 26, 2011 Share Posted March 26, 2011 No offense to FM power users but in Excel it's ridiculously simple. No offense to you, but it is ridiculously simple in Filemaker. However, I am not sure what exactly "03/10/2011 @ 14:26" means: are those two fields (one for date and one for time), or one timestamp field, or something else? Link to comment Share on other sites More sharing options...
Newbies mrmogway Posted March 26, 2011 Author Newbies Share Posted March 26, 2011 No offense to you, but it is ridiculously simple in Filemaker. However, I am not sure what exactly "03/10/2011 @ 14:26" means: are those two fields (one for date and one for time), or one timestamp field, or something else? the fields would be: start date (date field), start time (time field), end date (date field) end time (time field) no timestamp, all user input. the result in my example would be 3/14/2011 02:24 Link to comment Share on other sites More sharing options...
comment Posted March 26, 2011 Share Posted March 26, 2011 Try = Average ( Timestamp ( StartDate ; StartTime ) ; Timestamp ( EndDate ; EndTime ) ) Set the calculation's result type to Timestamp. the result in my example would be 3/14/2011 02:24 Perhaps you have made it too simple in Excel - because the correct result is 3/14/2011 1:54:00. Link to comment Share on other sites More sharing options...
Newbies mrmogway Posted March 26, 2011 Author Newbies Share Posted March 26, 2011 Try = Average ( Timestamp ( StartDate ; StartTime ) ; Timestamp ( EndDate ; EndTime ) ) Set the calculation's result type to Timestamp. Perhaps you have made it too simple in Excel - because the correct result is 3/14/2011 1:54:00. Yes yes yes ty, but... the result is present in one field. I need to break that up so that the date is in one field: mDate and the time in another: mTime. The reason being is because these are/will be included in calculations of I-131 in air particulates and charcoal cartridges. Link to comment Share on other sites More sharing options...
comment Posted March 26, 2011 Share Posted March 26, 2011 Change the calculation to = GetAsTimestamp ( Average ( Timestamp ( StartDate ; StartTime ) ; Timestamp ( EndDate ; EndTime ) ) ) for both fields. Set the result type to Date or Time as required. Link to comment Share on other sites More sharing options...
Newbies mrmogway Posted March 26, 2011 Author Newbies Share Posted March 26, 2011 Thank you for taking your time to help! I believed FM had a simple formula. I was searching in a different direction and didn't look into the Timestamp function. I have been using FM for years and have simply stayed with the functions I'm familiar with and made do. I will explore more and get up to speed. Thanks again. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 4778 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now