Excel formula help please?

[DrJohnZoidberg]

Just on a side note openoffice pales into comparison with MS Office. BUT there's always Kingsoft/WPS office which is great. This coming from a linux & oss supporter.

Unfortunate but true. I run MS Excel at the office using Crossover. That and Outlook because there just is no decent Linux mail app that does Exchange very well, especially calendar sync.

 

[czc]

The average thing should work. I have a spreadsheet with 220 months for my homeloan. But I also input the electricity each month. So I have 220 cells selected to be averaged. And it averages them. So far my average is 334. I also have conditional formatting on for high amounts. Just haven't managed to link the conditional formatting to the average value.

 

[I.am.Sam]

thread necro: Dont want to start a new thread

So i have few date and time stamps eg: 5/15/2018 7:50:23 PM

What i need is a formula to read the current time (live) and then give me whether the time string is <12hours, Between 12-24 hours , between 24-48, between 48-72 and > than 72

I need to add if and formula after

but not sure how to get the hours part from the time and date stamp

 

[I.am.Sam]

so just sat little with google and its a simple "Now()" formula ..

and minus the stamped time from the current time and multiply by 24 to give you a number

and do an if formula

anyone else have another method do share

 

[gregmcc]

Use =HOUR
So then to get the hour between 2 cells:
=HOUR(B2)-HOUR(A2)
One you have that you can implement the test, 12-24, 24-48 etc

 

[MidnightZA]

thread necro: Dont want to start a new thread

So i have few date and time stamps eg: 5/15/2018 7:50:23 PM

What i need is a formula to read the current time (live) and then give me whether the time string is <12hours, Between 12-24 hours , between 24-48, between 48-72 and > than 72

I need to add if and formula after

but not sure how to get the hours part from the time and date stamp

You can use the =hour mentioned above, then you will be limited to a 24 hour range.
You will also need to find the number of days between the 2 time strings and multiply by 24 to get the 48-72 ranges. The formula "=Days" should get you there

 

[I.am.Sam]

thanks guys

so i used the formula i posted and works well seeing you get the date and time when using the NOW formula

just minusing one time from the other and doing a multiple if statement works