Jun 07 2018 03:34 AM
My Dear Friends,
Any kind suggestions from you would be really, really appreciated!
Regards,
Your brother in distress :(
Jun 07 2018 04:15 AM
Hi Jayant,
If you'd only like to calculate the average you may use
=AVERAGE(<you cells>)*24*60
which will return the average in seconds.
Jun 07 2018 04:25 AM - edited Jun 07 2018 04:29 AM
Hi there,
excel assumes 2:16 is automatically hour:minutes; as you said, if you manually insert "00:" it understands that 2 is minutes and not hours.
I don't think adding "00:" manually in each cell should be worth it, so I'm asking IF you can add a column to the file, to do your job.
If you can, then it's easy!
=TEXT("00:"&TEXT(HOUR(A2);"00")&":"&TEXT(MINUTE(A2);"00");"[ss]")
It just convert a new string of values made by adding "00:" to the given datas (intended as hour and minutes) to convert it again in [seconds] after it understands that there are 3 slots of numbers, so it must be hours:minutes:seconds.
See attachement!
edit: or just like mr.Baklan said, just multiply the given data *24*60 and there will be the seconds. Then you'll make all ops you want with that data.
Jun 07 2018 04:35 AM
That also works
Jun 07 2018 05:21 AM
@Sergei Baklan wrote:Hi Jayant,
If you'd only like to calculate the average you may use
=AVERAGE(<you cells>)*24*60which will return the average in seconds.
Dear @Sergei Baklan,
I am so thankful to you. Honestly, I wasn't even sure anyone would even reply to my message!
Thanks so much for taking out time and replying to me - your solution worked beautifully!
I am smiling ear to ear as I thank you :)
God bless!
Jun 07 2018 05:24 AM
@Arul Tresoldi wrote:Hi there,
excel assumes 2:16 is automatically hour:minutes; as you said, if you manually insert "00:" it understands that 2 is minutes and not hours.
I don't think adding "00:" manually in each cell should be worth it, so I'm asking IF you can add a column to the file, to do your job.
If you can, then it's easy!
=TEXT("00:"&TEXT(HOUR(A2);"00")&":"&TEXT(MINUTE(A2);"00");"[ss]")
It just convert a new string of values made by adding "00:" to the given datas (intended as hour and minutes) to convert it again in [seconds] after it understands that there are 3 slots of numbers, so it must be hours:minutes:seconds.
See attachement!
edit: or just like mr.Baklan said, just multiply the given data *24*60 and there will be the seconds. Then you'll make all ops you want with that data.
Dear @Arul Tresoldi,
Thank you so much for your kind help! As I wrote to @Sergei Baklan, I wasn't even sure if anyone would even reply to my message!
You are really kind - just when I wasn't sure how to use the formula you shared, I see a lovely attachment, explaining how to use it. OMG! You're so cool!!
Thanks so much for taking out time and replying to me - your solution worked beautifully!
I am smiling ear to ear as I thank you :)
God bless!
Jun 07 2018 05:28 AM
Jayant, you are welcome
Oct 26 2020 02:23 PM
Oct 27 2020 02:16 PM
In Excel actually days are integers and time is decimal part of the number. In calculations one day is equal to 1, thus one hour = 1/24; one minute = 1/24/60 and one second = 1/24/60/60. With that you may do conversions from numbers to time and back, just apply proper format where necessary. Your sample:
Oct 27 2020 02:30 PM
@Sergei Baklan one wishes life will always be so easy, super, it is not just the formula that helped, but also the way approaching the problem, Thanks very much!
Oct 27 2020 03:00 PM
@jjrtoussaint-nl , you are welcome